Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |