Adding multiple KPI calculations together which contain long if statements
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]
