DAX Formula Grand Totals not calculating properly

[Anonymous]

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.

[Greg_Deckler]

@Anonymous 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

[adudani]

hi @Anonymous ,

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.