Forum Discussion
Cumulative Percent
I am trying to do a parento chart in Power BI and all of the guides I find seem very complicated. Can I just create a new measure where I calculate the cumulative percentage and insert that measure as a Line value in my graph to create a parento chart or is there a benefit to creating a new table or chart?
If so, how can I calculate the cumulative percentage based on a distinct count? I have an image
to help, with the values that I need. Please note that ID# 4 has 2 of the same discard reasons, but I only wish to count it once (this refers back to my main spreadsheet with a lot of data)
Looks like you've missed a parens.
CumulativeCount = CALCULATE([CountMeasure],FILTER(ALL('Table1'), 'Table1'[Referral Date] <= MAX('Table1'[Referral Date]) ))It's helpful if you tell us what error you're getting, or if you're getting an unexpected result when you say that a measure isn't working
4 Replies
You can definitely do this with a measure!
You need two things for a cumulative calculation. Data you're aggregating, and a way to determine how the data is sorted. You want to aggregate distinct counts which is great, but how does Power BI know that Serology comes before Culture, which comes before Time out?CountMeasure = COUNTROWS(GROUPBY('Table1', [Patient ID], [Discard]))CumulativeCount = CALCULATE([CountMeasure],FILTER(ALL('Table1'),<INSERT ORDERING LOGIC HERE>)Cumulative % = [CumulativeCount] / CALCULATE([CountMeasure], ALL('Table1'))The only thing left to do is to figure out how to tell PowerBI what order you want to do the sum in. When your categories can't be ordered correctly by themselves (like dates can), then this is usually done by creating some sort of index on a separate lookup table or using the sort by another column functionality.
Ok, so for the ordering logic, I want to sort it by the Referral date.
I have the rest of the dashboard already grouped by date, so I was unsure if I had to include it in the logic. So I have to put the filter in for my CumulativeCount equation?
My logic isn't working
CumulativeCount = CALCULATE([CountMeasure],FILTER(ALL('Table1', 'Table1'[Referral Date] <= MAX('Table1'[Referral Date])))Looks like you've missed a parens.
CumulativeCount = CALCULATE([CountMeasure],FILTER(ALL('Table1'), 'Table1'[Referral Date] <= MAX('Table1'[Referral Date]) ))It's helpful if you tell us what error you're getting, or if you're getting an unexpected result when you say that a measure isn't working