I have spent several days on this and still cannot figure out how to sort my data. To give better description, based on the KPI dashboard tutorial, my dashboard is for showing risk information.
There are 5 buttons:
1st = Risk Rank - this is a numerical value
2nd = Risk Category - text
3rd = Inherent Score - text (Medium, High, Low) based on numerical values for likelihood (9,7,5,1) and impact (9,7,5,1)
4th = Residual Score - same as inherent
5th = Velocity - text
The dashboard works perfect except for the Inherent and Residual ranks (i.e. High, Medium, Low). The problem occurs based the numerical/text values of the grid where risks are plotted. The Y-axis (Impact) starts at 1,3,5,7,9 and X-axis (Likelihood) starts at 1,3,5,7,9. Plotted on the chart, these values and their corresponding intersections are then summed. However, the assigned text ranking (i.e. High, Medium, Low) is subjectively assigned based on management discretion. Starting from the XY origin;
1:1=2=Low
1:3=4=Low
1:5=6=Low
1:7=8=Medium
1:9=10=High
3:1=4=Low
3:3=6=Low
3:5=8=Medium
3:7=10=Medium (This is where the problem occurs. As you can see the sum of 10 can be either High or Medium, depending on the assigned score).
Therefore, currently, when I sort the Inherent Score, anything that falls in the XY coordinate of 1:9 (High) will fall in the middle of the Medium scores.
I have tried many different formulas, based on the research from the site, but haven't found the solution. Any help is very appreciated.
