Tag Archives: KPI

PowerPivot KPI for Different Category Range

Suppose we have two simple related tables, ‘Table’ and ‘Service Code’. Main table contains transactions with accounts/dates/consumption amount by Service Code. The Service Code table contains the Min and Max ranges for service codes that will be used to analyze the average consumption. The Min/Max values differ for every service code and we want to create a KPI that will indicate if the average consumption falls within the range specified for the service code in question.

1. Import your data into the PowerPivot Model.

clip_image002

clip_image003

2. Create the relationships between the tables.

clip_image004

3. Create a calculated field for Average Consumption

clip_image005

4. Add two related columns for Min and Max values into the transaction table by using the PowerPivot RELATED() function. Note: this function will only work if you created the appropriate relationship in step 2. A good alternative for the RELATED() function is PowerPivot LOOKUPVALUE() if your tables are not related.

clip_image007

5. Add two calculated fields to find the minimum MIN value and maximum MAX value across the board using the MIN() and MAX() function on the respective columns.

clip_image009

6. Now for the fun part, create a calculated field that will provide us with a ‘Boolean’ value indicating if the Average Consumption (calculated field from step 3) falls between the Min and Max calculated fields from the previous step.

Consumption in range:=if([Average Consumption]=0,BLANK(),IF(AND([Average Consumption]>=[Service Code Min],[Average Consumption]<=[Service Code Max]),1,0))

clip_image011

7. Create KPI on the new calculated field.

clip_image013

8. Set the absolute value to 1 and move both min and max targets to 1 (that is 0.999 for the min value)

clip_image015

9. Cleanup the model by hiding the unnecessary columns/fields/table from client tools.

clip_image017

clip_image018

clip_image019

10. Create PivotTable.

clip_image020

11. The KPI Status column will provide the indicators showing if the average consumption falls in the ranges specified in the Service Code table for every service code.

clip_image022