Developing child-aware KPIs in PowerPivot and BISM Tabular
February 6, 2013 1 Comment
Sometimes you want to see if a parent level has violated a target value based on a comparison at the child level. For example, you want to browse a dashboard showing Product Categories and visually indicate which of those categories had Product Subcategories that violated a designated sales quota. Note that the quota is not assigned at the Product Category level, but the Subcategory level instead .
So we first must be sure to create a DAX expression that will decisively compute at the child level desired. In the example above, the target quotas would look like:
I am only showing a few of them but I hope the point is clear (also, for simplicity, I have not defined goal amounts per year, which is certainly doable).
For any given subcategory, we have a determinate ‘goal’ or target sales quota. As this table is now just a lookup in the model, we can connect it to the dimension with appropriate granularity (DimProductSubcategory)
The expression needed to see what we are comparing against at the Subcategory level is:
ISFILTERED ( DimProductSubcategory[EnglishProductSubcategoryName] ),
Sum ( FactResellerSales[SalesAmount] ),
Sum ( SubcategoryTargetAmount[Goal] )
The output of which is:
At this point we could think about using built-in KPI capabilities. In this scenario, however, this would not be helpful as KPIs, by default, compute at the level of granularity displayed and they also include subcategories even if the is no sales or goal data for them:
In the screenshot above, I removed the ISFILTERED condition of the Goal Amount DAX expression so it will show a value at the parent Category level. Notice that the status is green as the comparison – at that level – is favorable. This is *not* what we want: instead, it should be flagged as RED when any child level violated the goal amount (which is the case of the Accessories category shown).
In addition to that, and as mentioned before, note that the KPI is computed for all subcategories, whether they have sales and goals amount or not: again, not ideal.
Instead, we will be creating our own custom KPI. As such, there is a need to compute the ‘status’ value on our own through a DAX expression:
ISFILTERED ( DimProductCategory[EnglishProductCategoryName] ),
Values ( DimProductSubcategory[EnglishProductSubcategoryName] ),
[Sum of Sales Amount],
If ( [Sum of Sales Amount] < [Sum of Goal Amount], – 1, 0 )
The expression always computes at the Subcategory level (even at the Category level). It then rolls-up the amount of children that violated the goal (each of those will be assigned a value of –1).
When consuming the model through a pivot table, we can then use conditional formatting to mimic a regular KPI:
Notice that the rule is applied, not to a cell range, but to a measure (KPI). Also, the value assignment per icon may look a bit strange as it is designed to work with three value bands whereas here we only have two (negative or zero).
The final output properly display parent level alerts based on children level violations:
Its interesting to point out that event though there is 4 children that complied to the KPI rule and other 4 that did not, the parent level category does not “net out” the result. This is an important distinction and the reason why the DAX expression uses 0 for positive values instead of 1.