Hello,
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.
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.
