Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Running Total for Count Distinct Measure

I am trying to count distinct values in a by date and create a new measure that calculates the cumulative sum of the distinct counts as the time period progresses: The table on the left is representative of my data table. The table on the right is what I would like to my counts to look like.

 

 

Any help you can provide is appreciated!!

 

 

Need Help

4 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    6 years ago

    Assuming you have a calendar table

    https://exceleratorbi.com.au/power-pivot-calendar-tables/

    something like this

    Distinct products =DISTINCTCOUNT(data[product])

     

    Running total =CALCULATE(sumx(values(calendar[date]),[Distinct Products]),Filter(All(calendar),calendar[date] <= max(calendar[date])))

     

    use the calendar date column in your visual. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you! This worked.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous 

     

    I used the below to get the running total.

     

    CALCULATE (
    SUM ( 'Table'[Count Distinct Product] ),
    ALL ( 'Table' ),
    'Table'[Date] < EARLIER ( 'Table'[Date] )
)

     

     

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    Hi Anonymous ,

     

    Is this problem solved?

     

    If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

     

    If not, please let me know.

     

     

    Best Regards,

    Icey