help with Calculation

[kfschaefer]

Need  help with modifying the following Measure Calculation.

Compliance%Measure = CALCULATE(1 - ((SUM([OnboardedNo]) - SUM([CompliantNo]))) / SUM([Services]), ALL(IcM[Service Usage]))

Sum of SvcCount  where Exception is “InScope” –  sum of OnbaoredNo is 1 –  sum of CompliantNo is 1

divided by total rows with Exception is  “InScope”

What is the proper Dax syntax?

Thanks,

Karen

[v-jiascu-msft]

Hi @kfschaefer,

Could you please mark the proper answer as a solution?

Best Regards,
Dale

[v-jiascu-msft · Accepted solution]

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]

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