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

 

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

3 Replies

  • 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")
  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    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