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.**

 

I have concluded that my Matrix Table is calculating the correct amount of DistinctCount(MemberNumber) Total.  However, there is 1 MemberNumber that exists for both results "Existing" & "New" within the calculcated column.  Therefore, when my DAX is using DistinctCount for the entire column it gets the correct result of 468.  But, when it is using DistinctCount for each result "Existing" & "New" as separate rows, it's not seeing that overlapping MemberNumber and counts it as an additional DisctinctCount within the "New" row.  Please advise how to make each Row's filter/isolation take into account the entire Column's data.  

 

Current DAX:

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

 

The end result should read:

Existing = 433

New = 35

Total = 468

 

This is the current visual:

Thank you!! 

 

 

DAX
Slicer
sumx

5 Replies

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