Forum Discussion

Gkourtoglou's avatar
Gkourtoglou
Frequent Visitor
6 years ago
Solved

Cumulative Count

Good sirs. I have an excel file with Columns "Creation Date" (date format), "Week Number" (General), "Ticket ID" (General), "Year" (General) among plenty of others as seen on my first image below, an...
Need Help
Tips and Tricks
  • richbenmintz's avatar
    richbenmintz
    6 years ago

    Hi Gkourtoglou ,

     

    you can use a measure like 

    Weekly Cummulative = CALCULATE(sum('Table'[Tickets]), FILTER(ALLEXCEPT('Table', 'Table (3)'[Year]), 'Table'[Week] <= MAX('Table (3)'[Week])))

     

    you would have to change the sum to a countrows given you data, i just mocked it up with a sum.

     

    results below

    Hope this helps,

    Richard


    Did I answer your question? Mark my post as a solution!
    Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Gkourtoglou - There is a running total quick measure that might work. Also, you can get there if you have a measure using measure aggregation. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

 

You would put FILTER in to filter in only the stuff that is "before".

 

If none of that helps, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.