DAX – Switching Subtotals On & Off Dynamically Using DAX in PowerPivot

Guest Post by David Churchward

Subtotals on or off for a field – They’re the options that you get.  That’s probably great for a lot of situations, but I would put money on the majority of readers of this post having had a situation where they’ve asked “where’s the option to not display a subtotal when there’s only one record being shown?”.  Frustrating isn’t it?  You end up with a load of subtotals simply repeating the same value as it’s own one subset member because one of your other report elements has a subset of data that does need subtotalling.  You can, of course, toggle the show and hide for each member, but wouldn’t it be better if it was dynamic?

Fortunately, DAX has the answer.  It’s not the answer that I would have hoped for because this one feels like it should be an option setting somewhere.  However, we can deliver the same functionality by writing it into our measures.

In this post, I’ll explain how to create a dynamic measure that works out whether a subtotal is needed and displays that subtotal accordingly.  I’ll be using the report that was created as part of the Cash Flow Statement post.

The Subtotal Issue

Let’s refer back to the final report that I created for the Cash Flow Statement.

In the column CF3_Name, the only subtotal that I want to see is the one on Working Capital as this is the only one that has any lower level detail.  You’ll notice that all other areas are carrying subtotals  which only serves to repeat the number above it.

COUNTROWS and COUNTX

In order to solve this problem, you obviously need to know how many subset elements there are that would make up your subtotal.  It would be reasonable to jump straight for COUNTROWS.  Let’s examine that quickly with a measure that we’ll call CF4_Headings_No in the hope that it will tell us how many headings are in our subset.

CF4_Headings_No
= COUNTROWS(VALUES(CF4[CF4]))

On the face of it, that doesn’t appear to give us what we want.  We have the same results for all headings and CF4_Name repeats all of the same headings.  The reason that this happens is because CF3_Name and CF4_Name are held on separate tables.  Although they hold relationships to the same table, PowerPivot doesn’t pre-determine which combinations of these fields exist, even at run-time.

So let’s look at what happens when these headings are held on the same table with the same COUNTROWS formula (but obviously changing table name etc!).

That looks better right?  We can see all of the correct combinations of CF3_Name and CF4_Name.  On the one hand, this is good because we can distinguish the fact that Working Capital is the only element that has more than 1 subset item and therefore the only element that requires a subtotal.

However, there’s one big downside here.  For all other sections, we can’t distinguish between a subtotal line and a detail line.  There’s a couple more downsides here too:

1. I have to create a table that holds all of the combinations of these headings.  This isn’t the biggest issue as I have a table called GL_Headings that holds the codes associated to these headings.  I can create a RELATED()measure in my PowerPivot dataset to return the heading name.  However, why bother if we don’t have to?
2. Don’t underestimate the power of the function COUNTROWS(VALUES(TABLE[FIELD])) returning a value of 1 which happens when headings are held as a separate table.  This opens up a whole host of functionality and it’s probably one of the most important aspects of PowerPivot in my opinion.

So, what’s the final analysis of COUNTROWS

1. Using COUNTROWS against a separate table with unique values for each heading gives us the means to distinguish between subtotal sections and detail sections of our report.
2. Using COUNTROWS against a combined table with heading combinations tells us the CF3 headings that carry detail where we do want to see a subtotal.

I’m sure you’ve probably deduced where I’m going with this as the combination of these two elements probably tells us what we need to know to figure out whether a subtotal is required.  Unfortunately, this is not entirely true.  The problem is that the combination of these elements won’t operate in context unless the context is directly applied against the heading table (ie you put a slicer on the report for the cash flow heading elements).  What about a date slicer?  If you apply a date slicer which is a separate table, these measures won’t react.

Bring on COUNTX

Let’s jump straight into the DAX on this one and see what it’s doing by creating a measure called CF3_Total_Reqd.

CF3_Total_Reqd

=COUNTX(CF4,[Cash_Flow_Statement])

COUNTX is analysing our CF4 table in the context of our measure [Cash_Flow_Statement] (as calculated in the Cash Flow Statement post).  The context applied to [Cash_Flow_Statement] is carried across to our COUNTX formula.  We run this against our CF4 table because we want to get the number of detail heading names that carry a value in our [Cash_Flow_Statement] measure.

So what do we get:

Our CF3_Total_Reqd measure using COUNTX is telling us how many headings are used in our dataset for the MonthEndDate shown on our report.  This is exactly what we want.  What’s even more special is that if, in a particular month, our [Cash_Flow_Statement] measure only has one heading in the Working Capital section, the Working Capital Total would return a value of 1, which is a scenario when we want to switch our subtotal off.

But there’s a problem

Our COUNTX measure, as it stands, is returning a count where we have a heading, but it isn’t telling us when we have a valid blank entry at the CF4_Name level.  As a result, any CF3 category that doesn’t carry CF4 heading detail, isn’t telling us anything.  I know we have Operating Profit elements, but we’re not getting any counts returned.

But what if we specify the column that we’re analysing in our CF4 table?  We can do this by adding in VALUES and specifying the column in our CF4 table.

CF3_Total_Reqd
=COUNTX(VALUES(CF4[CF4_Name]),[Cash_Flow_Statement])

We have a result!  And our Working Capital section is behaving itself too!

And now for the Subtotal Switch On / Off Measure

Consider our two measures:

1. CF3_Total_Reqd this tells us when a total is required ie CF3_Total_Reqd > 1
2. CF3_Total_Reqd also tells us where detailed elements exist, ie returns a value of 1, but this measure does the same for those associated subtotals.  CF4_Headings_No, at the same time, helps us distinguish, generally and irrespective of context, between detail and subtotal levels on our report ie CF3_Total_Reqd = 1 AND CF4_Headings_No = 1

If we combine these elements, we get a measure that we’ll call Cash_Flow_Heading_Tidy

Cash_Flow_Heading_Tidy
=IF(
[CF3_Total_Reqd]>1||
([CF3_Total_Reqd]=1&&[CF4_Headings_No]=1),
[Cash_Flow_Statement],
BLANK()
)

That gives us what we want, right?  Well not quite.  The Cash Flow From Operations section is now right.  However, the other sections in my CF2_Name column have reached the criteria for their totals to be eliminated.  If I don’t want this effect (and I don’t because this is a major total), I have to specify for my Cash_Flow_Heading_Tidy to directly include those totals.

Cash_Flow_Heading_Tidy
=IF([CF3_Total_Reqd]>1||
([CF3_Total_Reqd]=1&&[CF4_Headings_No]=1)||

COUNTROWS(VALUES(CF3[CF3_Name]))>1,
[Cash_Flow_Statement],
BLANK()
)

There you have it.  All subtotals behaving properly.