Totals not calculating correctly using SumX

[mclawler]

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

[Greg_Deckler]

@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-TIT...

[mclawler]

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]

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

[mclawler]

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