KPI Logics

A common measure is a relative comparison between Actual and Plan like this formula:

([Actual]-[Plan])/[Plan]

For visualizing this measure as a KPI in a scorecard it is necessary to convert the measure to one of three values; 1 (green), 0 (yellow) or -1 (red). That could be done by using the iif-function as in the following example:

iif(([Actual]-[Plan])/[Plan] < -0.05, -1, iif(([Actual]-[Plan])/[Plan] > 0.05, 1, 0))

This formula will give red light (-1) when actual is more than 5% worse than plan and green light (1) when actual is more than 5% better than plan. If actual is between -5% and 5% compared to plan the formula will give yellow light (0).

But, when calculating KPIs it is often necessary to handle different types of inconsistent data and relative comparisons between values over and below zero. Division by zero must be handled and it is also desired to have one place for storing different bandings. The following formula will address those demands:

iif([Actual] = [Plan], 0,
iif([Plan] = 0, iif([Actual] - [Plan] > 0, 1, -1),
iif(([Actual] - [Plan]) / ABS([Plan]) < [Banding Stoplight Red], -1,
iif(([Actual] - [Plan]) / ABS([Plan]) > [Banding Stoplight Green], 1, 0))))

When Plan is zero this formula gives green light if Actual is better than Plan and it gives red light if Actual is worse than Plan independent on how the bandings are defined.