Detecting Total, Subtotal and Hierarchy Levels in PowerPivot
February 20, 2012 12 Comments
Recently I participated on a thread on the PowerPivot MSDN forums in which it was asked how to detect the level you are on in the pivot table hierarchy in order to conditionally handle the output of a measure. As I think this is a fairly simple topic but one that can be a bit confusing, I hope this post can help others dealing with the very same issue. Thanks to SQL Server MVP Frederik Vandeputte (twitter) for the topic! 🙂
In general, it has become a standard practice to use the following expression if you want to avoid computing it at the ‘Grand Total’ level:
IF( COUNTROWS( VALUES( Table1[Column] ) ) = 1, <expression> )
For example, if you have a measure that should not be aggregated as in the table below:
You could create the following DAX measure to display the value for each product, and avoid any output at the total level:
As you can see, the pivot table ignores the grand total even if it was configured to show one. With PowerPivot 2.0 (Currently in RC0), you can use the HASONEVALUE function to make the expression even more readable:
HASONEVALUE( Table2[Products] ) ,
VALUES( Table2[Size] )
The output will be the same in both expressions. The idea here is that we are checking the current filter context for how many Products are currently active in the cell that is evaluating the expression. It will only yield an output for those pivot table cells that have one and only one Product in context. From here, you can see why the Grand Total is ignored: it deals with more than one product.
This is all well known. The question is, can we apply the same technique to detect levels of a hierarchy? Here you must remember that hierarchies are ‘display-only’ groupings in PowerPivot (and SSAS BISM Tabular). Even with the first release of PowerPivot, row labels are grouped in a ‘hierarchical’ way based on the order of attributes you place on the row labels:
If we follow the logic we used from Grand Totals, we can infer that a subcategory can have more than one product. In a similar way, we are aware that a category can have more than one subcategory. Here is the dataset:
Following this logic, we can create a DAX measure to help us determine which level we are dealing with – product, subcategory or category:
COUNTROWS(VALUES(Table1[Subcategories])) = 1,”SubCategory”,”Category”
Unfortunately, this doesn’t give the correct result:
For Category A / SubCategory A the calculation works without a problem as there are two products (which allows us to detect the SubCategory level) and two SubCategories (which allow us to detect the Category level).
However, for Category B there is only one Product. As the expression detects a level based on count of attribute values below the current location in the display hierarchy, it has no way to know which level is it on in this case.
So how can we fix it? Fortunately, this is once again a situation in which the ability to manipulate context comes handy. I thank my good friend and colleague Jason Thomas (blog | twitter ) who helped me think of a way to deal with these kind of scenarios.
If we use the following DAX measure, we get the correct values:
ALLEXCEPT(Table1, Table1[Products])) = 1,
ALLEXCEPT(Table1, Table1[Subcategories])) = 1,
The inner CALCULATE clears the filter context on all columns except subcategory. As such, it doesn’t limit itself to count the number of subcategories for the current category; instead it counts all subcategories across all categories. In this way, even if a category only has one subcategory (like CategoryB in the example above) it won’t be a problem as the count will rely on the subcategories across the entire table. The same logic is used by the outer CALCULATE when determining if the current level is as product or not.
The only situation in which this wont be the case would be, for example, if there is only row in the table showing one product, one subcategory and one category. It is unlikely this type of situation will affect most reporting scenarios, but you should be aware of this limitation.
Also, is it interesting to notice that the Grand Total is detected as being on the ‘Category’ level. This, again, shouldn’t be a problem as normally you would want your most encompassing level in the Total row. And here once again you can change the behavior by combining the expression used with the technique we discussed at the beginning of this blog entry.
Finally, notice that by detecting the current level we will be able to assign a specific subtotal or total calculation which depends on the level which is active in the filter context. For example, we can add another DAX measure like the one below which leverages the expression we used to generate conditional outputs:
IF([CurrentLevel] <> “Product”,
SUM( Table1[Amount] ) * 0.9,
On it, the category and subcategory levels will be discounted by 10% due to specific reporting conditions (perhaps a wholesale promotion, etc.)
UPDATE: Alberto Ferrari pointed out to the ability to use a simpler syntax in DAX 2.0 leveraging the ISFILTERED function.
Interestingly, when using this new function in DAX 2.0 we avoid the issue described earlier in this post in which a table with a single row will give the incorrect level output. First, lets review the syntax using ISFILTERED:
ISFILTERED(Table2[Subcategories]), “Subcategory”, IF(
ISFILTERED(Table2[Categories]), “Category”, “Category” )
As you can see, the formula is much readable now. Now, lets see what happens when we compare both expressions (the one using ALLEXCEPT and the one using ISFILTERED):
Using ISFILTERED yields the expected output in a table with one row whereas the expression using ALLEXCEPT doesn’t. This is because we are evaluating the current level in a different way: ISFILTERED is able to detect direct filters which will yield a more accurate result.
Consequently, if you have the opportunity to use the latest version of PowerPivot (Currently in RC0), this last calculation is definitely the way to go. Thanks Alberto for the suggestion.