Forum Discussion
Totals not calculating correctly using SumX
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:
4 Replies
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/434008Hi 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)))mclawler Maybe try swapping out your SUMX for ADDCOLUMNS and then return the table using TOCSV to see what is actually going on.
