Cumulative Percent

[Anonymous]

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)

https://imgur.com/XK025bI

[Cmcmahan]

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.

[Anonymous]

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])))

[v-piga-msft]

Hi @Anonymous ,

The suggestion of Cmcmahan should be helpful.

Have you solved your problem?

If you have 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 you still need help, please share more data sample which should inclue the Referral Date so that we could help further on it.

Best  Regards,

Cherry

[Cmcmahan · Accepted solution]

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