Forum Discussion

mclawler's avatar
mclawler
Helper III
2 years ago

DistinctCount with individual Rows Summing incorrectly

**Moderators, please remove the previous thread I created titled "Totals not calculating correctly using SumX" as I have dug much deeper into this issue and realized the actual root of the problem.**...
DAX
Slicer
sumx
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi mclawler 

 

lbendlin  Thank you very much for sharing!

 

This is a very suitable way, here I provide some specific code. You can try the following code:

MemberNumberDISTINCTCOUNT =
SUMX (
    VALUES ( Heloc3MonthAdvances[TransactionDateMonth] ),
    DISTINCTCOUNT ( Heloc3MonthAdvances[MemberNumber] )
)

 

MemberNumberDISTINCTCOUNT_Existing =
CALCULATE (
    [MemberNumberDISTINCTCOUNT],
    Heloc3MonthAdvances[Result] = "Existing"
)

 

MemberNumberDISTINCTCOUNT_New =
CALCULATE (
    [MemberNumberDISTINCTCOUNT],
    Heloc3MonthAdvances[Result] = "New",
    EXCEPT (
        VALUES ( Heloc3MonthAdvances[MemberNumber] ),
        CALCULATETABLE (
            VALUES ( Heloc3MonthAdvances[MemberNumber] ),
            Heloc3MonthAdvances[Result] = "Existing"
        )
    )
)

 

This should give you the results you want, which is 433 for "Existing" and 35 for "New".

 

EXCEPT function (DAX) - DAX | Microsoft Learn

 

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mclawler's avatar
mclawler
Helper III
2 years ago

Thank you for the reply I think you're on the right track but it's giving me blanks.  Currently I use a calculated column to generate the rows for Existing/New:

 

Existing/New = IF(Heloc3MonthAdvances[OpenDateMonth] < Heloc3MonthAdvances[TransactionDateMonth],"Existing HELOC","New HELOC")

 

 

Any idea how to modify your code to adapt to my rows?  I have to keep my rows becuase in the same table I use other measures for $ Sum and YoY, MoM, etc... Thank you so much for your help! 

 

I tried these with no success:

MemberNumberDISTINCTCOUNT_Existing =
CALCULATE (
    [MemberNumberDISTINCTCOUNT],
    Heloc3MonthAdvances[Existing/New] = "Existing"
)
 
MemberNumberDISTINCTCOUNT_New =
CALCULATE (
    [MemberNumberDISTINCTCOUNT],
    Heloc3MonthAdvances[Existing/New] = "New",
    EXCEPT (
        VALUES ( Heloc3MonthAdvances[MemberNumber] ),
        CALCULATETABLE (
            VALUES ( Heloc3MonthAdvances[MemberNumber] ),
            Heloc3MonthAdvances[Existing/New] = "Existing"
        )
    )
)