Score based on variable metric ranges

[Anonymous]

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!

[Anonymous]

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.

[v-yinliw-msft · Accepted solution]

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.

[Anonymous]

Thank you! I was really having trouble looking up based on a range of values

you're a lifesaver!

[BeaBF]

@Anonymous Hi!

Can you explain the fields in the matrix/table you want to obtain?

BBF