Forum Discussion
DAX for Summarizing at an aggregate level
I have two tables. One with the total sale amount for the month by car and another with the details showing car, model, and sales person. I have merged the to tables together and need to find a way to write dax that gives me the sum of the amount sold without over counting. **NOTE in my example I do not have the ability to split these tables out into two tables and relate them so I need a DAX that can work off the belnded fact table (Table 3).
I need a DAX measure that sums the amount for cars sold. In table 1 below the total amount that was sold was $2400. (1000 + 200 + 400 + 800). You can see on the green merged table 2 below those amount values are duplicated and I need to be able to sum the column, but sum the unique values by car.
Sales Amount for the month by Car (Table 1)
Sales Detail (Table 2)
Table 3: Blended Fact Table (Main Fact Table I need DAX built off of)
Here is the formula I have showing the "INCORRECT" value....
Sales Amount:=SUM(Table3[Amount])
7,200 is not the right amount, it should be 2,400 for the month.
I need a dax formula that shows me this....
How do you write dax off Table 3 that gives you the table above showing 2,400, if the amount values from the first table are duplicated when merged with the second table?
Anonymous First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
Try:
Measure 2 = VAR __Table = GROUPBY('Table5',[Car],"__Avg",AVERAGEX(CURRENTGROUP(),[Amount])) VAR __Result = SUMX(__Table,[__Avg]) RETURN __Result
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8
3 Replies
- Greg_Deckler
Community Champion
Anonymous First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
Try:
Measure 2 = VAR __Table = GROUPBY('Table5',[Car],"__Avg",AVERAGEX(CURRENTGROUP(),[Amount])) VAR __Result = SUMX(__Table,[__Avg]) RETURN __Result
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8 - AnonymousNot applicable
Yep that worked! Curious if there is another way to write the formula without the Average? Could you leverage SUMMARIZE somehow?
- Greg_Deckler
Community Champion
Anonymous Well, there are a couple ways of going about it but that way was the simplest for your scenario. SUMMARIZE can get you in trouble sometimes.