Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 ...
  • v-yinliw-msft's avatar
    v-yinliw-msft
    3 years ago

    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's avatar
Anonymous
Not applicable
3 years ago

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's avatar
v-yinliw-msft
Icon for Community Support rankCommunity Support
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

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

    you're a lifesaver!