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.
Good Morning all, I've read a number of posts on a number of forums that all point to "issues" (I get that it's actually calculating properly based on the way the table reads the formula) with Grand Totals not calculating properly with DAX formulas but I am a bit stumped as I've moved SUMX across a number of formulas to no avail.
Here's what I'm trying to do.
I have a DAX formula named "Total New Customers" that calculates the total number of new customers on table LY_SS_REF and then subtracts the total of those customers that purchased. The formula used is below.
Total New Customers:=CALCULATE(DISTINCTCOUNT(LY_SS_REF[CUSTOMER_ID]))-CALCULATE(DISTINCTCOUNT(LY_SS_REF[CUSTOMER_ID]),FILTER(LY_SS_REF,[PURCHASED]="YES"))
In the adjacent column ("Total Customer Increase"), I'm trying to caculate (rounded up to the next whole number) a 10% increase off of the first column "Total New Customers". The value of 10% is stored in the table/column 'SS_Increase_Target[New Customers SS increase].
Total Customer Increase:=ROUNDUP([Total New Customers]/100*VALUES(SS_Increase_Target[NEW Customers SS increase:]),0)
When I sum up the total amount, the formula calculates "correctly" but it does not sum up the totals in either column properly. You will see in the screenshot below where the Grand Total in the second column correctly calculates 10% of the Grand Total in the first however, when you sum up the individual columns, the total is roughly 200 units off (969 vs. 1,168).
Wisdom is much appreciated.
@ManeMan First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
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
hi @ManeMan ,
I think it is not calculating correctly because distinct count is a semi-additive measure.
In order to achieve the result on the totals, you might have to force additivity if required.
kindly refer to (7) Why Power BI totals might seem wrong - YouTube (15 mins -23/25 mins ) that might be relate to this context.
Appreciate a thumbs up if this helps.
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
46 | |
31 | |
30 | |
18 | |
18 |