The tabular model and power pivot (CTP) allows the quick creation of KPI’s. This quick post looks at how KPI’s are created and viewed in Excel.
Once a measure is defined in the measure grid, a KPI can be created from it. This is done by selecting the cell that contains the base measure (ie the value cell) and selecting ‘Create KPI’ from the menu bar (or ‘Create KPI’ from the fast menu (ie, right click à create KPI)). This invokes the KPI manager dialog as below. Note that the base measure is defined and although this is selectable, it is not editable.
To create the KPI, we only need to define the target measure (assuming that we are basing the base value against some other measure) and (perhaps) edit the thresholds for which the KPI value is determined.
For example, to create the KPI against budget we would select the [Sales Bgt] calculation from the target value drop down (as below). [Sales Bgt] has already been defined as a measure (not a column in a table). Setting the thresholds is done by sliding the marker arrows (see below) or entering the value directly into the threshold box (as highlighted).
The threshold determines the upper and lower bound of the KPI severity based on the relationship of the [base measure]/[target measure]. For this example, thresholds at 95% and 100% will display an amber icon when the value of [Sales Act]/[Sales Bgt] is between 95% and 100%. The direction of severity (red, amber, green) or (green, amber, red) is controlled by selecting one of the four types (identified below). When a value does not appear in the base value, the KPI is not shown.
|Descriptions can be added to each component of the KPI by Expanding the descriptions area
Descriptions are shown as tooltips when the item is hovered over in excels pivot field list.
View in Excel
|The Creation of the KPI appends three measures in the pivot field list (see right). When these are added as to the pivot, the base measure name is suffixed with the function that the measure (and derived measure) plays in the KPI. For example, the value of the KPI base measure of [Sales Actual] becomes [Sales Actual Value].
The original measure is lost (that is there is no measure [Sales Actual]).
Overall, the tabular model is a very simple and intuitive technology and the creation of KPI are no exception to this. Further reading can be found at books on line;
Once the KPI has been created (and the base measure has been suffixed), the model does not support a new calculation with the same name as the base measure. For example, there could be no measure titled ‘Sales Act’.