Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there,
I'm trying to replicate the output of an Excel pivot table by using DAX. But I'm brand new to DAX.... 🙂
Here is my pivot table:
and here are my 3 tables:
The pivor shows the average of claims[amount] for each people[sex], grouped by people[age].
What I got until now in DAX is this:
EVALUATE SUMMARIZECOLUMNS( people[age], FILTER(people,people[gender]="f"), "Avg_female", AVERAGE(claims[amount]) ) ORDER BY people[age]
which generates this table:
This is the first column of my pivot, how can I add the average of male gender?
Finally, I will need to filter the results (by using slicers and timelines), so if you can show me how to filter the final table based on counties[state] and claims[date] that would be great!
Thank you for your time!
Adrian
Solved! Go to Solution.
Hey,
not sure if I fully understand your requirements, but I would start with something similar to this:
EVALUATE SUMMARIZECOLUMNS( people[age], "Avg_female", CALCULATE(AVERAGE(claims[amount]),FILTER(people,people[gender]="f")) ,"Avg_male", CALCULATE(AVERAGE(claims[amount]),FILTER(people,people[gender]="m")) ) ORDER BY people[age]
Maybe this is what you are looking for.
Regards,
Tom
Hey,
not sure if I fully understand your requirements, but I would start with something similar to this:
EVALUATE SUMMARIZECOLUMNS( people[age], "Avg_female", CALCULATE(AVERAGE(claims[amount]),FILTER(people,people[gender]="f")) ,"Avg_male", CALCULATE(AVERAGE(claims[amount]),FILTER(people,people[gender]="m")) ) ORDER BY people[age]
Maybe this is what you are looking for.
Regards,
Tom
Hi Tom,
You understood perfectly, that's exactly what I need, thank you!
I just need one more thing, to use slicers and timelines for filtering, which means adding two general filters:
Looks like I can do it by adding the filter in each gender block, like this:
EVALUATE SUMMARIZECOLUMNS ( people[age], "Avg_female", CALCULATE ( AVERAGE ( claims[amount] ), FILTER ( people, people[gender] = "f" ), FILTER ( counties, counties[state] = "Texas" ) ), "Avg_male", CALCULATE ( AVERAGE ( claims[amount] ), FILTER ( people, people[gender] = "m" ), FILTER ( counties, counties[state] = "Texas" ) ) ) ORDER BY people[age]
but is there any way to filter the whole table at once?
Thanks again!
Hey,
maybe this is what you are looking for:
EVALUATE CALCULATETABLE( SUMMARIZECOLUMNS ( people[age], "Avg_female", CALCULATE ( AVERAGE ( claims[amount] ), FILTER ( people, people[gender] = "f" ) ), "Avg_male", CALCULATE ( AVERAGE ( claims[amount] ), FILTER ( people, people[gender] = "m" ) ) ) ,FILTER ( counties, counties[state] = "Texas" ) ) ORDER BY people[age]
Regards,
Tom
I'm getting a "SummarizeColumns() and AddMissingItems() may not be used in this context." error when trying the last code.
Any other approach? If not, I will stick with filters for each group....
I managed to get it working by replacing SUMMARIZECOLUMNS with SUMMARIZE. Here is the code:
EVALUATE CALCULATETABLE( SUMMARIZE ( claims, people[age], "Avg_female", CALCULATE ( AVERAGE ( claims[amount] ), FILTER ( people, people[gender] = "f" ) ), "Avg_male", CALCULATE ( AVERAGE ( claims[amount] ), FILTER ( people, people[gender] = "m" ) ) ) ,FILTER ( counties, counties[state] = "Texas" ) ) ORDER BY people[age]
Thanks for your help, you put me on the right track!
Can you post sample data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Honestly, I'm not sure you have to do all of this work in DAX versus just using a Matrix visualization and maybe some simple measures.
Sorry, here is a couple of rows of data for each table.
I'm trying to do a table based on a custom DAX directly in Excel, by using Excel's data model.
counties:
id | county_code | county_name | state | state_abbrev | population |
2489 | 48001 | Anderson | Texas | TX | 9876 |
2490 | 48003 | Andrews | Texas | TX | 6170 |
2491 | 48005 | Angelina | Texas | TX | 1222 |
people:
person_id | county_code | age | weight | gender |
1545191196 | 8027 | 9 | 104 | m |
9996085812 | 48309 | 9 | 280 | m |
1039327894 | 48197 | 9 | 139 | m |
claims:
claim_id | person_id | date | amount | treatment_days |
10000005 | 0022707752 | 1/1/2006 | 438.71 | 8 |
10000111 | 1115974936 | 1/1/2006 | 918.17 | 8 |
10000139 | 1481138056 | 1/1/2006 | 460.03 | 8 |
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |