When we published our first Income Statement in Power BI, it didn’t take long for someone to ask if we would also include additional custom subtotal lines such as Gross Margin [Sales – Cost of Goods] in the matrix visual.
In this post I’ll share two options that give us flexibility in creating custom subtotals. The first method is called Cascading Subtotals, the other method I refer to as Isolated Subtotals to bring greater flexibility to using subtotals.
The Data Model
Our sample data model contains just what you see below, a small factGL and dimAccount table. The factGL table below includes just enough transaction data to calculate two sample subtotals, Margin and Margin Less Payroll.
The second table is a partial chart of accounts and serves as our Account dimension table. We relate the two tables to each other through the common Account field in both tables. The dimAccount table helps:
- Determine the order of how GroupName and LineName fields both display in the matrix,
- Provides the source of our subtotal label names and their relative display position (Group Order) in the matrix.
Once the tables are related, the next step is to set the sort order for both the Group Name as well as the Line Name field of the dimAccounts table.
We highlight GroupName and using the Sort by Column in the ribbon to sort GroupName by Group Order. We do the same for LineName and LineOrder. This ensures that our matrix visual reports Sales above Cost of Goods, Margin below Cost of Goods, and so on.
Cascading Subtotal
The first subtotal method is cascading subtotals, and goes way back to a 2011 Power Pivot Pro post by David Churchward here. I’ve modified it slightly to work with sorted dimension columns in Power BI (defined above). I also combine multiple expressions into one measure with the help of DAX variables.
The need to modify this for the sort order stems from a difference in the underlying query in Power BI vs. the MDX query in Power Pivot. Basically, the Power BI version of the query includes the ‘sort by column’ in the underlying query, where the same query in Power Pivot does not. To learn more about the impact in Power BI, and the simple fix, check out this new video from our friends at SQLBI.
The main driver for cascading subtotals is the use of a flag column in the account dimension table. When we write the subtotal measure, we look to a variable called Flag to define each unique subtotal row in the dimAccounts table. When the evaluation matches a flag row equal to 1, the subtotal expression kicks in, otherwise the default SUM expression is used.
When the Subtotal variable is needed, the expression below identifies the current row in the dimAccount table and uses all prior rows to filter the factGL table appropriately.
For Margin this would include all account Group Order lines less than 2.5, which are Sales and Cost of Goods. For the Margin Less Payroll subtotal, this would include all account Group Order lines less than 3.5.
Here is the full measure:
1 – Identifies each subtotal row in the dimAccount table that contains a max value of 1, and is used to activate the subtotal expression.
2 – Identifies the current Group Order number in our dim Accounts table. When in a subtotal row, this number is used below to define and filter the range in the subtotal expression.
3 – Sets up the subtotal expression when flag = 1 by summing the full range in the fact table where Group Order numbers (defined in 2) are less than the current row.
4 – It is important to note here that the only reason that our self defined subtotals appear at all in the matrix, is due to the FILTER ALL table expression that overwrites all group names found in the dimAccounts table, including Margin and Margin Less Payroll. Otherwise there is nothing in the fact table to evaluate to our custom subtotals, and the subtotal names would be skipped in the matrix.
Also note that we include two columns in our ALL function here (GroupOrder and GroupName). This accomplishes two things:
1. It accounts for the group Order field in our underlying Query, that is required in Power BI but not in Power Pivot.
2. By isolating only these two columns (vs. the entire table) under ALL(), we avoid duplicating all the detail above each subtotal row. Otherwise we’d drill-in to the subtotals and re-access all of the detail as reported above the subtotal row. This especially becomes an issue if your report readers export data into Excel.
This specific table expression (dimAccounts[GroupOrder] <__maxline) also works dynamically and works just the same when calculating Margin Less Payroll, except the range sets itself to capture everything less than Group Order 3.5.
This method is ideal for subtotals that work off of sequential reported groups like an Income statement. If we need to work with non-sequential groups in generating subtotals, we’d use another method to accomplish this same result.
Isolated Subtotal Method
Imagine you wanted to calculate KPI ratios and place them in specific rows in your report, or group all subtotals at the bottom of your matrix. The prior example wouldn’t work as it depends on sticking with the order sequence in the dimension table.
An alternative is to calculate dedicated measure expressions for each subtotal, then use a separate SWITCH statement to determine when and where to report each defined subtotal. To do so, we split our measure up into two sections.
This first section holds variables that define each custom expression used on our matrix.
Since we are not filtering between a range of sequential values here, we can use a Boolean filter (dimAccounts[GroupName]=“Sales”) rather than the more extensive FILTER ALL table filter we used above. This format is shorter to write out, but still overwrites all of the group names including the ones defined as subtotals.
Also notice that we add the REMOVEFILTERS modifier to this measure. This serves a similar purpose to using ALL() in the first example, as it addresses the use of Group Order in the underlying query of Power BI.
By limiting the removal of filters to only Group Order, we also avoid duplicate data appearing in the subtotal rows in the matrix and exported data.
The second section is where we define the expression, and where it displays on the matrix. Since we have isolated expressions, we can combine or divide them together as needed. The position displayed in the matrix still depends on the sequence order in our dimAccounts table, but this time the order does not dictate the result of the expression.
To view the full measure, download the pbix file and look for the measure: 1. Subtotal_Isolated.
The advantage of this method would provide greater control over where and how subtotal are expressed while the disadvantage would be having to define each subtotal expression separately.
This post walked through two options in creating custom subtotal rows in the matrix visual, Cascading Subtotals and Isolated Subtotals. My hope is that this allows you to better work with the matrix visual and provide a more traditional reporting format for your report readers.
As always, thanks for reading! and Happy New Year! – Mark
Leave a Reply