topic Re: Totals not calculating correctly using SumX in DAX Commands and Tips

Totals not calculating correctly using SumX

mclawler — Tue, 06 Feb 2024 16:54:05 GMT

Hey there, love this community as it's been a huge help along my career path. I have a scenario where only these 2 totals are not calculating correctly, and I've used SumX in the past with success but it doesn't appear to be working this time. Please advise.

Neither of these DAX metrics are calculating the total correctly:

MemberNumberDISTINCTCOUNT =

SUMX (

VALUES ( Heloc3MonthAdvances[TransactionDateMonth] ),

DISTINCTCOUNT ( Heloc3MonthAdvances[MemberNumber] ))

MemberNumberYTD DISTINCTCOUNT =

SUMX (

VALUES ( Heloc3MonthAdvances[TransactionDate].[Month]),

TOTALYTD(

DISTINCTCOUNT('Heloc3MonthAdvances'[MemberNumber]),DateTable[Date]))

Re: Totals not calculating correctly using SumX

Greg_Deckler — Tue, 06 Feb 2024 17:06:22 GMT

mclawler Try replacing VALUES with DISTINCT as you might be getting a blank row returned that has a value being included in your total.

Also, You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Re: Totals not calculating correctly using SumX

mclawler — Tue, 06 Feb 2024 17:26:39 GMT

Hi Greg, I tried replacing VALUES with DISTINCT with no success. I have generated other source reports and the Total should be 469, somehow the Total row is not capturing everything in the column above it (468)?

To make things even more confusing, these 3 DAX are working correctly with the same data table and same rows as above:

MemberNumberPrevious_Month_DISTINCTCOUNT = CALCULATE(DISTINCTCOUNT('Heloc3MonthAdvances'[MemberNumber]),DATEADD('DateTable'[Date],-1,MONTH))

MemberNumberPrevious_Year_DISTINCTCOUNT = SUMX (

VALUES ( Heloc3MonthAdvances[TransactionDate].[Month]),CALCULATE(DISTINCTCOUNT('Heloc3MonthAdvances'[MemberNumber]),DATEADD('DateTable'[Date],-1,YEAR)))

MemberNumberYoYTD DISTINCTCOUNT = SUMX (

VALUES ( Heloc3MonthAdvances[TransactionDate].[Month]),

TOTALYTD(

DISTINCTCOUNT('Heloc3MonthAdvances'[MemberNumber]),

DATEADD('DateTable'[Date],-1,YEAR)))

Re: Totals not calculating correctly using SumX

Greg_Deckler — Tue, 06 Feb 2024 21:11:20 GMT

mclawler Maybe try swapping out your SUMX for ADDCOLUMNS and then return the table using TOCSV to see what is actually going on.

Re: Totals not calculating correctly using SumX

mclawler — Tue, 06 Feb 2024 23:50:17 GMT

Ok, I really did some digging on the data and turns out I was mistaken from the getgo but I still have an issue.

The correct Total is 468. The Correct Existing HELOC row is 433. The incorrect New HELOC row in reality should be 35 instead of 36. So my Row is showing an additional Disctinct Count. Would it be counting the Column Header maybe? I'm not sure where it's picking up the additional 1 item. Sorry for the previous confusion