When we published our first Income Statement in Power BI, it didn’t take long for someone to ask if we could include rows for custom subtotals such as Gross Margin [Sales – Cost of Goods] in the matrix visual.
In this post I’ll share two options that we used in creating custom subtotals. The first method is called Cascading Subtotals that depend on sequential row groupings. The other method is called Isolated Subtotals and provides better flexibility and placement of subtotals in your matrix visual.
The Data Model
Our sample data model contains a small factGL and dimAccount table as shown below. The factGL table below includes just enough transaction data to calculate two sample subtotals, Margin and Margin Less Payroll.
factGL Table
The second table is our Account dimension table. We relate the two tables to each other through on the common Account field key shown in both tables.
The below dimAccount helps us:
- 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.
dimAccount Table
Once the two tables are related, we set the sort order of our DimAccount table for both the Group Name as well as the Line Name field of the dimAccounts table.
Highlight GroupName and using the Sort by Column in the ribbon to sort GroupName by Group Order as shown below. 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.
Now that we have our columns sorted, lets start creating subtotals!
Option 1: Cascading Subtotal
I’ve modified this first version published here in 2011 to work specifically with sorted dimension columns in Power BI. Click on this video to learn more about the need for our sort order method used above. I also break our measure out with variables to better manage the measure.
The magic that makse the cascading subtotal work is the use of a flag column placed in the account dimension table. When we write the subtotal measure, we will look to the Flag column to define each subtotal row in the dimAccounts table, and turn on the part of the measure that creates our subtotal for that given row.
FactGL Table
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.
DimAccount Table
Here is the full measure:
1 – Identifies each subtotal row in the dimAccount table that contains a flag value of 1. This flag value is used to activate the subtotal expression.
2 – Identifies the current filter context or Group Order number in our dim Accounts table. When in a subtotal row, this number is used below to define and filter the line number to sum up to in our fact table.
3 – Sets up the subtotal expression when our flag value is equal to 1, summing the full range in the fact table where Group Order numbers don’t exceed our max line number defined in step 2.
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. FILTER ALL overwrites all group names currently filtered 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 from sequential reported groups like an Income statement. If we need to work with non-sequential groups in generating subtotals, we’d use another method I call ‘Isolated Subtotals’ shown next.
Option 2: Isolated Subtotal Method
Imagine you want to calculate KPIs 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 a 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