Forum Discussion
Problem Calculating weighted KPI's with different target values (DAX Measure)
Hello Power BI Community,
My first post here 🙂 so applogies if any mistakes
I'm encountering a challenging issue with a DAX measure in Power BI, and I could use some advice. The goal is to calculate an overall achievement score based on three KPIs, each with different target levels and weights. Here are the details:
KPIs and Targets:
- Application KPIs: Target 95%, Weight (7/9)
- UAM KPI: Target 60%, Weight (1/9)
- ADSS KPI: Target 100%, Weight (1/9)
Problem: Despite implementing a capping mechanism in my DAX formula to limit each KPI's contribution to 100%, the overall score still exceeds 100%. For instance, with the KPI values at 93.94% (Application KPI), 74.24% (UAM KPI), and 93.75% (ADSS KPI), the overall score incorrectly calculates to 101.07%. This not only breaches the 100% cap but also that the calculated value is higher than expected. The total score should be well within 100%, considering the provided KPI performances.
Current Approach:
- Standardize each KPI by dividing the actual value by its target.
- Cap the result at 100% using IF statements.
- Apply weights to the capped values.
- Sum the weighted scores, ensuring the total does not exceed 100%.
Here’s the DAX formula currently being used:
KPI Achievement =
VAR ApplicationKPI = [- Application KPIs %] / 100
VAR UAMKPI = [- UAM KPI %] / 100
VAR ADSSKPI = 'ADSS KPI'[ADSS KPI %] / 100
VAR ApplicationTarget = 95 / 100
VAR UAMTarget = 60 / 100
VAR ADSSTarget = 100 / 100
VAR StandardizedApplicationKPI = ApplicationKPI / ApplicationTarget
VAR StandardizedUAMKPI = UAMKPI / UAMTarget
VAR StandardizedADSSKPI = ADSSKPI / ADSSTarget
VAR CappedApplicationKPI = IF(StandardizedApplicationKPI > 1, 1, StandardizedApplicationKPI)
VAR CappedUAMKPI = IF(StandardizedUAMKPI > 1, 1, StandardizedUAMKPI)
VAR CappedADSSKPI = IF(StandardizedADSSKPI > 1, 1, StandardizedADSSKPI)
VAR ApplicationWeight = 7 / 9
VAR UAMWeight = 1 / 9
VAR ADSSWeight = 1 / 9
VAR WeightedTotalScore =
(ApplicationWeight * CappedApplicationKPI) +
(UAMWeight * CappedUAMKPI) +
(ADSSWeight * CappedADSSKPI)
VAR FinalScore = IF(WeightedTotalScore > 1, 1, WeightedTotalScore)
RETURN FinalScore * 100
- Request: I need guidance on:
- Understanding why the formula might allow the score to exceed 100% despite the capping logic.
- Identifying any flaws in the logic that could cause the values to be higher than expected.
For clarity I've also included an example of one of the referenced KPI Measures (nothing is wrong here, this looks correct, formatted it as a percentage)
- Application KPIs % =
VAR _TotalRows = COUNTROWS(ALLSELECTED('CR KPI'))
VAR _OKRows = COUNTROWS(FILTER(ALLSELECTED('CR KPI'), [Application KPIs] = "OK"))
RETURN
DIVIDE(_OKRows, _TotalRows, 0)
I appreciate any insights or suggestions the community can provide. Thank you!
Best regards,
Ross
--- ADDED EXAMPLE VALUES
| Sub-Measure Name | Example Value | Expected Result with KPI Achievement measure | Explanation of Logic |
| [- Application KPIs %] | 95% | 100% | The department met all of the targets (set above) so the result of the KPI Achivement should be 100% |
| [- UAM KPI %] | 60% | (see above) | |
| 'ADSS KPI'[ADSS KPI %] | 100% | (see above) | |
| --- | --- | --- | --- |
| [- Application KPIs %] | 95% | 100% | This time the department did well, the exceeded the KPI, but achievement is only measured up to 100% so no higher on the score (this helps as if a department score higher than the target in one KPI area, the achievement KPI will not be impacted until all of the targets are met) |
| [- UAM KPI %] | 80% | (see above) | |
| 'ADSS KPI'[ADSS KPI %] | 100% | (see above) | |
| --- | --- | --- | |
| [- Application KPIs %] | 80% | <100% (exact value will be figured out when the calculation works) | This time the department did not meet the 95% target in application KPI's so there score is less than 100 (despite meeting/exceeding KPIs in other areas) |
| [- UAM KPI %] | 80% | (see above) | |
| 'ADSS KPI'[ADSS KPI %] | 100% | (see above) |
I managed to fix it, I used a different logic with IF statements. Appreciate any comments on the DAX writing quality so I can improve (new to this)
Actual Target Achievement Score = -- Set the targets VAR Application_Target = 0.95 VAR UAM_Target = 0.6 VAR ADSS_Target = 1 -- Ensure that anything over the target is capped, so that it doesn't impact other KPI achivements (e.g. If you score higher than target in one KPI and haven't met the target in another area, you don't achieve the overall goal) VAR Application_Capped_Total = IF([- Application KPIs %] > Application_Target, Application_Target, [- Application KPIs %]) VAR UAM_Capped_Total = IF([7. IAM3 - UAM KPI %] > UAM_Target, UAM_Target, [7. IAM3 - UAM KPI %]) VAR ADSS_Capped_Total = IF([ADSS KPI %] > ADSS_Target, ADSS_Target, [ADSS KPI %]) -- Weight the KPI's (between 9 KPIs) VAR Application_KPI_Weighted = Application_Capped_Total * (7/9) -- Note if any one of the 7 application KPI's are red, then the application scores 0% for the application KPIs (as defined) VAR UAM_KPI_Weighted = UAM_Capped_Total * (1/9) VAR ADSS_KPI_Weighted = ADSS_Capped_Total * (1/9) VAR Total_Achievement_Score = Application_KPI_Weighted + UAM_KPI_Weighted + ADSS_KPI_Weighted RETURN Total_Achievement_Score
3 Replies
Hi,
Share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file. You may alternatively, share the download link of the file. Show the problem clearly there and also the expected result.
Thanks for the response Artur, the underlying data would be very simple. So I just included a table below quickly. You could replace the referenced measures with these values hard coded.
Note: On the "See above" lines, these are all sub-measures that should be combined into to provide the result of the overall measure I am having a problem with.Sub-Measure Name Example Value Expected Result with KPI Achievement measure Explanation of Logic [- Application KPIs %] 95% 100% The department met all of the targets (set above) so the result of the KPI Achivement should be 100% [- UAM KPI %] 60% (see above) 'ADSS KPI'[ADSS KPI %] 100% (see above) --- --- --- --- [- Application KPIs %] 95% 100% This time the department did well, they exceeded the KPI, but achievement is only measured up to 100% so no higher on the score (this helps as if a department score higher than the target in one KPI area, the achievement KPI will not be impacted until all of the targets are met) [- UAM KPI %] 80% (see above) 'ADSS KPI'[ADSS KPI %] 100% (see above) --- --- --- [- Application KPIs %] 80% <100%
(exact value will be figured out when the calculation works)This time the department did not meet the 95% target in application KPI's so there score is less than 100 (despite meeting/exceeding KPIs in other areas) [- UAM KPI %] 80% (see above) 'ADSS KPI'[ADSS KPI %] 100% (see above) I managed to fix it, I used a different logic with IF statements. Appreciate any comments on the DAX writing quality so I can improve (new to this)
Actual Target Achievement Score = -- Set the targets VAR Application_Target = 0.95 VAR UAM_Target = 0.6 VAR ADSS_Target = 1 -- Ensure that anything over the target is capped, so that it doesn't impact other KPI achivements (e.g. If you score higher than target in one KPI and haven't met the target in another area, you don't achieve the overall goal) VAR Application_Capped_Total = IF([- Application KPIs %] > Application_Target, Application_Target, [- Application KPIs %]) VAR UAM_Capped_Total = IF([7. IAM3 - UAM KPI %] > UAM_Target, UAM_Target, [7. IAM3 - UAM KPI %]) VAR ADSS_Capped_Total = IF([ADSS KPI %] > ADSS_Target, ADSS_Target, [ADSS KPI %]) -- Weight the KPI's (between 9 KPIs) VAR Application_KPI_Weighted = Application_Capped_Total * (7/9) -- Note if any one of the 7 application KPI's are red, then the application scores 0% for the application KPIs (as defined) VAR UAM_KPI_Weighted = UAM_Capped_Total * (1/9) VAR ADSS_KPI_Weighted = ADSS_Capped_Total * (1/9) VAR Total_Achievement_Score = Application_KPI_Weighted + UAM_KPI_Weighted + ADSS_KPI_Weighted RETURN Total_Achievement_Score
