Detecting Total, Subtotal and Hierarchy Levels in PowerPivot

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:

image

You could create the following DAX measure to display the value for each product, and avoid any output at the total level:

CalcAtProductLevelOnly:=
IF(
COUNTROWS(VALUES(Table2[Products]))=1,
VALUES(Table2[Size])
)

image

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:

CalcAtProductLevelOnly:=
IF(
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:

image

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:

image

Following this logic, we can create a DAX measure to help us determine which level we are dealing with – product, subcategory or category:

IF(
COUNTROWS(VALUES(Table1[Products]))=1,”Product”,
IF(
COUNTROWS(VALUES(Table1[Subcategories])) = 1,”SubCategory”,”Category”
)
)

Unfortunately, this doesn’t give the correct result:

image

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:

IF(
CALCULATE(COUNTROWS(VALUES(Table1[Products])),
ALLEXCEPT(Table1, Table1[Products])) = 1,
“Product”,
IF(
CALCULATE(COUNTROWS(VALUES(Table1[Subcategories])),
ALLEXCEPT(Table1, Table1[Subcategories])) = 1,
“SubCategory”,
“Category”
)
)

image

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:

ConditionalLevelOutput:=
IF([CurrentLevel] <> “Product”,
SUM( Table1[Amount] ) * 0.9,
SUM(Table1[Amount])
)

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:

IF(
    ISFILTERED(Table2[Products]),”Product”, IF(
        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):

image

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.

Advertisements

12 Responses to Detecting Total, Subtotal and Hierarchy Levels in PowerPivot

  1. @Javier, have you considered an easier implementation using ISFILTERED in DAX 2.0? This technique is very nice and works fine in the current release but it might be useful to expand it using ISFILTERED in SQL 2012, as all the formulas will be much easier.

  2. javierguillen says:

    Thanks Alberto. You are correct, the technique described works well in PowerPivot 1.0; for those users that have to opportunity to use PowerPivot 2.0 in SQL Server 2012, the solution using ISFILTERED is a great option: not only the formula is easier and more legible, it actually has an advantage as described on the blog update I posted.

  3. Dan says:

    I am having a problem with this approach. It definitely works in Pivot Tables, but when I use a DAX query it does not seem to work. The query below always returns the bottom level.

    Define
    Measure ‘Date'[TimeLevel] =
    if(
    IsFiltered(‘Date'[Date]),
    “Date”,
    if(
    IsFiltered(‘Date'[Month]),
    “Month”,
    if(
    IsFiltered(‘Date'[Calendar Quarter]),
    “Calendar Quarter”,
    if(
    IsFiltered(‘Date'[Calendar Year]),
    “Calendar Year”,
    “Total”
    ))))

    evaluate(
    Summarize(
    ‘Internet Sales’,
    Rollup(
    ‘Date'[Calendar Year],
    ‘Date'[Calendar Quarter],
    ‘Date'[Month]
    ),
    “Time Level”, ‘Date'[TimeLevel]
    )
    )
    Order By
    ‘Date'[Calendar Year],
    ‘Date'[Calendar Quarter],
    ‘Date'[Month]

    • javierguillen says:

      Hi Dan,

      Using SUMMARIZE automatically converts propagates context in the table (or table expression) used in the first parameter. As such, ISFILTERED won’t behave in the same way as in a pivot table. When using DAX queries, I suggest relying on the first technique mentioned (using COUNTROWS):

      evaluate
      summarize(
      DimDate,
      rollup(
      DimDate[CalendarYear],
      DimDate[EnglishMonthName])

      ,”Level”,

      IF(
      CALCULATE(COUNTROWS(VALUES(DimDate[EnglishMonthName])),
      ALLEXCEPT(DimDate, DimDate[EnglishMonthName])) = 1,
      “Month Level”,
      IF(
      CALCULATE(COUNTROWS(VALUES(DimDate[CalendarYear])),
      ALLEXCEPT(DimDate, DimDate[CalendarYear])) = 1,
      “Year Level”,
      “All Level”
      )
      )

      )
      order by
      DimDate[CalendarYear],
      DimDate[EnglishMonthName]

      Alternatively, you can use the ISSUBTOTAL function to detect the rolllup level.

  4. Pingback: SCOPING at different granularities in DAX (Part I) « Javier Guillén

  5. Pingback: SCOPING at different granularities in DAX (Part I) « Javier Guillén

  6. Horia says:

    Hi,
    This doesn’t work properly if you have a filter added on a level.
    IF(
    ISFILTERED(Table2[Products]),”Product”, IF(
    ISFILTERED(Table2[Subcategories]), “Subcategory”, IF(
    ISFILTERED(Table2[Categories]), “Category”, “Category” )
    )
    )
    for example if there is a filter on a specific “subcategory” this will return “Subcategory” for every upper level.

    • javierguillen says:

      Hi Horia

      When you place an additional filter (through a slicer, for example) on a subcategory then the category level will *only* show data on the selected subcategory. In that sense, it has been “filtered” for that subcategory and even though you are the category level, the output value you see is the one associated with the subcategory. This would be the expected behavior in many scenarios; but I do understand there are some exceptions in which you may not want that.

      • Horia says:

        Yes, I would like to keep the totals for the upper levels and not be filtered by the filter I’ve set on a lower level. It should show me something like ALL for the upper levels and only the filter data for the filtered level. I hope it’s clear enough :).

  7. Horia says:

    Hello,
    I’ve managed to get the report in the way I need. In the pivottable options –> in Total & filters –> there is an option ‘include filtered itmes in total’, so this one removed the filtered for the totals rows.
    thank you very much for the previous reply (I didn’t mention it in my previous reply)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: