Forum Discussion

wi11iamr's avatar
wi11iamr
Icon for Advocate II rankAdvocate II
10 years ago
Solved

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 i...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

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.

wi11iamr's avatar
wi11iamr
Icon for Advocate II rankAdvocate II
10 years ago

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!