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.
2. Create the relationships between the tables.
3. Create a calculated field for Average Consumption
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.
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.
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))
7. Create KPI on the new calculated field.
8. Set the absolute value to 1 and move both min and max targets to 1 (that is 0.999 for the min value)
9. Cleanup the model by hiding the unnecessary columns/fields/table from client tools.
10. Create PivotTable.
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.