Score based on variable metric ranges

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.