cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Dynamically Calculate Percentage for Line in "Line & Stacked Column" chart

I have a stacked column chart showing the number of incidents In and Out of SLA, per Month.

I would now like to convert to a Line & Stacked Column chart, where the Line is the percentage of In SLA incidents, for that specific month.

My challenge is how do I create a measure that takes the total number of In and Out SLA values for each month, and then does a separate percentage calculation for each separate month.

If my understanding is correct, this percentage calculation must happen within the report itself, and I cannot do it within the query/dataset, as the percentage value must change depending on which date dimension I select for my Chart (E.g. Month, Quarter etc)

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Dynamically Calculate Percentage for Line in "Line & Stacked Column" chart

Try something like:

MyMeasure = COUNT([Incidents])/CALCULATE(COUNT([Incidents]),ALL([Table]))

Putting that measure in your visualization should filter the numerator by the axis (month) but the denominator will always include all incidents because of the ALL clause. Thus, you *should* end up with what you want.

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

2 REPLIES 2
Super User

## Re: Dynamically Calculate Percentage for Line in "Line & Stacked Column" chart

Try something like:

MyMeasure = COUNT([Incidents])/CALCULATE(COUNT([Incidents]),ALL([Table]))

Putting that measure in your visualization should filter the numerator by the axis (month) but the denominator will always include all incidents because of the ALL clause. Thus, you *should* end up with what you want.

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Regular Visitor

## Re: Dynamically Calculate Percentage for Line in "Line & Stacked Column" chart

@SM thanks for the pointer!

I think I originally had something similar to what you suggested, however when approaching it with fresh eyes and your suggested code, I managed to also figure out (aka "guess") that while I was hoping to count the number of "Yes" in my InSLA column, my percentage always worked out to 100%, and this is because the COUNTA function was counting the blank values as well. I thus switched to using COUNTBLANK and subtracting the result from 1, as below.

PercentageInSLA = 1- (COUNTBLANK([InSLA])/CALCULATE(COUNTA([CaseNumber]),ALL(CRMCases[CaseNumber])))

Thanks again!

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 44 members 868 guests
Recent signins: