Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 a...
Need Help
  • Cmcmahan's avatar
    Cmcmahan
    7 years ago

    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

Cmcmahan's avatar
Cmcmahan
Resident Rockstar
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

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

     

     

     

    • Cmcmahan's avatar
      Cmcmahan
      Resident Rockstar
      7 years ago

      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

    • v-piga-msft's avatar
      v-piga-msft
      Resident Rockstar
      7 years ago

      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