April 1, 2019
I am struggling in creating a Pivot Table based on two data sets I have - please find attached.
Tab "Population" contains a dammy data of population information.
Tab "Distribution" contains a dammy data of an individual person receiving goods.
The common denominator in both tabs are the column "Household Reference".
What I would like to extract from Pivot Tables are as follow to create a Dashboard. Based on "Household Reference" in "Distribution" tab, for each location:
- No. of people covered (which I have already)
- No. of goods distributed.
- No. of people by Male/Female.
- No. of people by Gender.
- No. of people by Male/Female and by Gender.
- No. of people by Disability, Pregnant, Lactating and Single Household.
I have create a relationship linking "Household Reference" on both tabs but I realised I need to use DAX? I tried all day to understand but nothing is working for me. I would really appreciate your support!!! Thank you.
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Excel Customer Service
November 8, 2013
Hi, for simple questions like this, you can combine the 2 tables into 1 using Power Query, then create simple pivot tables, like in the file attached.
DAX is for much more complex scenarios. To create power Pivots, you need to create a calendar table, a lookup table with distinct Reference ID's (no duplicates), create relationships then write measures for each question.