Forum Discussion

mclawler's avatar
mclawler
Icon for Helper III rankHelper III
2 years ago

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 b...
"DAX help"
"Grand Total"
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

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

mclawler's avatar
mclawler
Icon for Helper III rankHelper III
2 years ago

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)))

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

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

    • mclawler's avatar
      mclawler
      Icon for Helper III rankHelper III
      2 years ago

      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