Forum Discussion

PowerBIzNess's avatar
PowerBIzNess
Frequent Visitor
8 years ago

Cumulative Distinct Count by Month

Hello,    This is driving me crazy and is probably something simple that I'm overlooking..   Trying to create a calculated column that counts the distinct # of employees to date.             Cu...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

PowerBIzNess,

You can use the following DAX to calculate cumulative. Make sure that  you choose "Don't summarize" for cumulative column in table visual.

Cumulative = CALCULATE(DISTINCTCOUNT('YTD Attrition'[Employee]),ALL('YTD Attrition'),'YTD Attrition'[Term Date]<=EARLIER('YTD Attrition'[Term Date]))



Regards,
Lydia

  • PowerBIzNess's avatar
    PowerBIzNess
    Frequent Visitor
    8 years ago

    Anonymous

     

    thanks so much for the reply. I realized my issue was the summarization in the visual, which displayed correctly when I selected 'Average'. For some reason the 'Don't Summarize' option doesn't appear as an option - I think this is because in order to gather some additional metrics I unpivoted a list of columns detailing attrition reasons, which created multiple rows for the same individuals. So for example my data looks like:

     

    John Doe    5/1/2017   Compensation

    John Doe    5/1/2017   Relocation

    John Doe    5/1/2017   Benefits

     

    Any suggestions you have on improving this would be appreciated!

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      PowerBIzNess，

      The DAX also works in your scenario, please check the following screenshot.


      Regards,
      Lydia

      • PowerBIzNess's avatar
        PowerBIzNess
        Frequent Visitor
        8 years ago

        Thanks Lydia

         

        What is your default summarization for the column? I don't have a 'Don't Summarize' option so I'm left having to average the numbers.