Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wi11iamr
Advocate II
Advocate II

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
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
jk1185
Frequent Visitor

In case we have multiple factors more than 4 or 5, can we do the same thing for multi-factors above 4-5. Can we do it without writing multiple measures for each factor?

Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.