Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Could someone help me please with my DAX calculation, it's not working out the way I expect.
I have a number of KPIs and I'm evaluated them against a target and if they meet the target I flag them with 1.
I want to sum all the KPIs that have met target but when I do they all return 1.
Ultimately I would like to calculate a percentage of the KPIs that have met the Target. So in the case below it should be 50% of KPIs have met target.
The KPITargetMet Measure works, but the TargetMetSum isn't working properly. When I create a percentage it says 100% have made target, which is incorrect.
Here is the DAX code I'm using for the Measures.
KPITargetMet =
VAR KPIType = SELECTEDVALUE(MeasureData[KPITargetDiffDirectionUpper])
VAR KPIMeasure = [Measure]
VAR KPITarget = [Target]
RETURN
IF(KPIType="HIGHER", IF(KPIMeasure >= KPITarget, 1, 0),
IF(KPIType="LOWER", IF(KPIMeasure <= KPITarget, 1, 0),0))
TotalKPI = CALCULATE(DISTINCTCOUNT(MeasureData[KPIsCode]))
TargetMetSum = CALCULATE(DISTINCTCOUNT(MeasureData[KPIsCode]), FILTER(MeasureData, [KPITargetMet]=1))
Hope this make sense.
Thanks Angelo
Solved! Go to Solution.
@angelo2022 , KPI Met Target measure
Sumx(Values(Table[KPI Code], if( [KPITargetMet]=1, [TotalKPI] , blank()))
@angelo2022 , KPI Met Target measure
Sumx(Values(Table[KPI Code], if( [KPITargetMet]=1, [TotalKPI] , blank()))
Hi @amitchandak, That worked great for calculating the percentage for the month, thanks for that.
But I can't get it to display well in a YTD line chart. Only displays the current month selected.
Here is the DAX
Hi @amitchandak, Don't worry about my last question about the YTD. I've worked it out. Thanks again mate.
User | Count |
---|---|
53 | |
35 | |
20 | |
15 | |
14 |
User | Count |
---|---|
94 | |
76 | |
36 | |
22 | |
18 |