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