Forum Discussion
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, and I import this excel file on Power BI.
Excel Description
Each row is one request from a client.
To find "Week Number", I use =TEXT(ISOWEEKNUM([@[Creation Date]]);"00")
To find "Year", I use =YEAR([@[Creation Date]])
Power BI
I count the column "Ticket ID" with axis "Week Number" in order to get how many request I have from clients each week and use filters accordingly.
Cumulative Count
I would like to create a graph like the second image below, where Axis is Week Number and each value is the cumulative count of each week, comparing year by year.
Week by Week count
Cumulative Count
After numerous months of research and fails, I humbly ask you senseis for some guidance 🙂
Best regards,
George
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!
4 Replies
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.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!
