Forum Discussion

kfschaefer's avatar
kfschaefer
Helper IV
8 years ago
Solved

help with Calculation

Need  help with modifying the following Measure Calculation. Compliance%Measure = CALCULATE(1 - ((SUM([OnboardedNo]) - SUM([CompliantNo]))) / SUM([Services]), ALL(IcM[Service Usage]))     Sum of ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Karen,

     

    Try this formula, please.

    Compliance%Measure =
VAR onboardedNo =
    CALCULATE (
        SUM ( [OnboardedNo] ),
        table1[OnboardedNo] = 1,
        table1[exception] = "InScope"
    )
VAR compliantNo =
    CALCULATE (
        SUM ( [CompliantNo] ),
        table1[CompliantNo] = 1,
        table1[exception] = "InScope"
    )
VAR total =
    CALCULATE ( SUM ( [Services] ), table1[exception] = "InScope" )
RETURN
    CALCULATE (
        1
            - ( onboardedNo - compliantNo )
            / total,
        ALL ( IcM[Service Usage] )
    )

    Best Regards,

    Dale

Seward12533's avatar
Seward12533
Solution Sage
8 years ago

Karen would avoide using special charcaters in your meaure names and it messes up intellisense for  auto populating underscore is OK but % will really cause problems. Also use the safe DIVIDE function versus / for division.

 

But I think its somethink like the followign but really need to take some time to learn the basics of PowerBI.  

 

Compliance Measure = CALCULATE(
   DIVIDE(1 - SUM(table[OnboardedNo] - SUM(table[CompliantNo]),COUNTROWS(table[Services]))
, ALL(IcM[Service Usage],table[Exception]="InScope")