Forum Discussion
Score based on variable metric ranges
Hi everyone! Need help with measures/column formulas/data modelling to achieve the below table/matrix :
| Month | KPI 1 Actual | KPI 1 Score | KPI 2 Actual | KPI 2 Score | KPI 3 Actual | KPI 3 Score | Total Score |
| October | 98.00% | 50.00% | 91.00% | 25.00% | 75.00% | ||
| November | 91.00% | 20.00% | 89.00% | 0.00% | 3.50 | 30.00% | 50.00% |
Aside from the other table which contains the actual scores, this would be the table for the variable ranges which is expected to change every month :
| Month | KPI | Metric | Min Range | Max Range | Score |
| October | 1 | Attendance | 98.00% | 100.00% | 50.00% |
| October | 1 | Attendance | 95.00% | 97.99% | 30.00% |
| October | 1 | Attendance | 0.00% | 94.99% | 10.00% |
| October | 2 | Productivity | 95.00% | 100.00% | 50.00% |
| October | 2 | Productivity | 90.00% | 94.99% | 25.00% |
| October | 2 | Productivity | 0.00% | 89.99% | 0.00% |
| November | 1 | Attendance | 94.00% | 100.00% | 30.00% |
| November | 1 | Attendance | 89.00% | 93.99% | 20.00% |
| November | 1 | Attendance | 0.00% | 88.99% | 10.00% |
| November | 2 | Productivity | 95.00% | 100.00% | 40.00% |
| November | 2 | Productivity | 90.00% | 94.99% | 20.00% |
| November | 2 | Productivity | 0.00% | 89.99% | 0.00% |
| November | 3 | AHT | 5.00 | 0.00 | 30.00% |
| November | 3 | AHT | 10.00 | 5.01 | 15.00% |
| November | 3 | AHT | 10.01 | 10.01 | 10.00% |
Thanks in advance!
Hi Anonymous ,
You can try this method:
New for columns:
KPI 1 Score = CALCULATE(SUM('Table'[Score]), FILTER('Table','Table'[KPI] = 1 && 'Table (2)'[KPI 1 Actual] >= 'Table'[Min Range] && 'Table (2)'[KPI 1 Actual] <= 'Table'[Max Range] && 'Table (2)'[Month] = 'Table'[Month]))KPI 2 Score = CALCULATE(SUM('Table'[Score]), FILTER('Table','Table'[KPI] = 2 && 'Table (2)'[KPI 2 Actual] >= 'Table'[Min Range] && 'Table (2)'[KPI 2 Actual] <= 'Table'[Max Range] && 'Table (2)'[Month] = 'Table'[Month]))KPI 3 Score = CALCULATE(SUM('Table'[Score]), FILTER('Table','Table'[KPI] = 3 && 'Table (2)'[KPI 3 Actual] <= 'Table'[Min Range] && 'Table (2)'[KPI 3 Actual] >= 'Table'[Max Range] && 'Table (2)'[Month] = 'Table'[Month]))Total Score = 'Table (2)'[KPI 1 Score] + 'Table (2)'[KPI 2 Score] + 'Table (2)'[KPI 3 Score]The result is:
Hope this helps you. Here is my PBIX file.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Anonymous Hi!
Can you explain the fields in the matrix/table you want to obtain?
BBF
Hi! So for the KPI Actual columns, the actual data would come from another table. These actual scores will then be scored based on their respective KPI range. In the example the KPI 1 Actual for October (Attendance) is 98%, so looking at the variable table, it falls within the range of 98% to 100%, thus the score of 50% on the KPI 1 Score column. For November, the example has 91% for KPI 1 Actual, which falls under the 89% - 93.99% range, thus the score of 20% in the KPI 1 Score column. These KPI Scores will then be totaled for the final score.
Hi Anonymous ,
You can try this method:
New for columns:
KPI 1 Score = CALCULATE(SUM('Table'[Score]), FILTER('Table','Table'[KPI] = 1 && 'Table (2)'[KPI 1 Actual] >= 'Table'[Min Range] && 'Table (2)'[KPI 1 Actual] <= 'Table'[Max Range] && 'Table (2)'[Month] = 'Table'[Month]))KPI 2 Score = CALCULATE(SUM('Table'[Score]), FILTER('Table','Table'[KPI] = 2 && 'Table (2)'[KPI 2 Actual] >= 'Table'[Min Range] && 'Table (2)'[KPI 2 Actual] <= 'Table'[Max Range] && 'Table (2)'[Month] = 'Table'[Month]))KPI 3 Score = CALCULATE(SUM('Table'[Score]), FILTER('Table','Table'[KPI] = 3 && 'Table (2)'[KPI 3 Actual] <= 'Table'[Min Range] && 'Table (2)'[KPI 3 Actual] >= 'Table'[Max Range] && 'Table (2)'[Month] = 'Table'[Month]))Total Score = 'Table (2)'[KPI 1 Score] + 'Table (2)'[KPI 2 Score] + 'Table (2)'[KPI 3 Score]The result is:
Hope this helps you. Here is my PBIX file.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
