I have table which contains a value based upon a percent range. I was able to calculate the percent performance and assign performance points for each individual user in the load editor. I have no problem displaying this value when an individual user is selected. I want to calculate and display the performance points in the same display based upon the performance points of all users when no single user is selected. The average of all users does not give me the results I need. I have attempted to calculate the performance points within the KPI calculation, but ran into problem due to the multiple if statements. The end goal is to add multiple performance points together to obtain an overall performance score. This the first step.
Here is what I have done thus far.
| USER | NUMERATOR_MET_COUNT | DENOMINATOR_COUNT | PERCENT_PERFORMANCE | PERFORMANCE_POINTS |
|---|
| A | 185 | 203 | .91 | 10 |
| B | 448 | 535 | .84 | 9 |
| C | 142 | 241 | .60 | 6 |
| D | 394 | 456 | .86 | 9 |
| E | 333 | 359 | .93 | 10 |
Here is what I want to see in KPI.
When user A is selected: 10
When user B is selected: 9
etc...
When all user as selected (no filter applied) then the value should be 9 based upon the a PERCENT_PERFORMANCE of .83 (sum NUMERATOR_MET_COUNT / sum DENOMINATOR_COUNT). Here is the code I entered in the load editor.
...
NUMERATOR_MET_COUNT / DENOMINATOR_COUNT AS [PERCENT_PERFORMANCE]
IF([PERCENT_PERFORMANCE]>= .01 AND [PERCENT_PERFORMANCE] <.11 , '1',
IF([PERCENT_PERFORMANCE]>=.11 AND [PERCENT_PERFORMANCE]<.21, '2',
IF([PERCENT_PERFORMANCE]>=.21 AND [PERCENT_PERFORMANCE]<.31, '3',
IF([PERCENT_PERFORMANCE]>=.31 AND [PERCENT_PERFORMANCE]<.41, '4',
IF([PERCENT_PERFORMANCE]>=.41 AND [PERCENT_PERFORMANCE]<.51, '5',
IF([PERCENT_PERFORMANCE]>=.51 AND [PERCENT_PERFORMANCE]<.61, '6',
IF([PERCENT_PERFORMANCE]>=.61 AND [PERCENT_PERFORMANCE]<.71, '7',
IF([PERCENT_PERFORMANCE]>=.71 AND [PERCENT_PERFORMANCE]<.81, '8',
IF([PERCENT_PERFORMANCE]>=.81 AND [PERCENT_PERFORMANCE]<.91, '9',
IF([PERCENT_PERFORMANCE]>=.91, '10', 0)))))))))) AS [PERFORMANCE_POINTS]
...
