Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Hi @kfschaefer,
Could you please mark the proper answer as a solution?
Best Regards,
Dale
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
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")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |