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.
Sample Data Model
Our sample data model contains a small GL transaction table (1GL) and FINORDER account dimension table as shown below. The fact GL table below includes just enough transaction data to calculate two sample subtotals, Margin and Margin Less Payroll.
Table 1: 1GL Fact (Transaction) Table
Table 2: FINORDER Dimension Table
The second table is our Account dimension table named FINORDER. We relate the two tables to each other through on the common STMT DESC and STATEMENT DESC fields shown in both tables.
The FINORDER account dimension helps:
- Define the order of how Group Name (Group column) and Line Name (Line column) fields both display in the matrix, using corresponding sort order columns Group Ord and Order columns respectively.
- Provides the source of our new subtotal lines and their relative display position (Group Order) in the matrix. See row 20 below that defines the new Margin subtotal line positioned as group number 2.5.
Once the two tables are related in this example through the STMT DESC TRIM line (normally based on an account key), we set the sort order for both the Group Name as well as the Line Name field in our FINORDER table.
Highlight Group and using the Sort by Column in the ribbon to sort Group by Group Ord as shown below. Do the same for Line and Order. 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, let’s start creating subtotals!
Option 1: Cascading Subtotal
I’ve modified this first version published by P3 (Power Pivot Pro) 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 updated measures to use more current methods including variables for better organization and performance.
The magic behind cascading subtotals is the combination of a well-structured measure and the use of a flag column placed in the account dimension table (FINORDER table).
When our filter context hits the specific row in the FINORDER table that has a Flag value of 1, our measure will convert from a simple filtered SUM to a SUM that opens up the filter context to include all account lines above the current flagged subtotal line i.e. Margin.
For Margin below, 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.
FINORDER Table
Here is the full measure including variables:
1 – Identifies each subtotal row in the FINORDER 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 Ord number in our FINORDER table. When in a subtotal row, (defined by Flag=1) this Group Ord number is used to filter all rows (defined by order number) that come before in in our FINORDER table.
3 – Sets the conditions that must be true to activate the __Subtotal variable. When we hit a Flag row in FINORDER, and we are not evaluating a grand total line (where there are multiple groups filtered, aka no single group filtered) this variable evaluates including opening up the filters currently over FINORDER table as addressed below is step 4.
4 – Note that the relationships between FINORDER and our fact table 1GL evaluate results based on valid relationships and filters between the two tables. In order to have subtotals injected into the matrix, we need to ignore relationships at some level to prevent active filtering of the fact table. In this example we remove filters over the Group level, but not the Line level. By removing filters over Group, we turn filtering off, allowing the Margin group name, and its measure result to display on the matrix.
However, at the Line field level, we left the filter (and relationship) active. This prevents evaluation, as the active filter (through relationships) filters zero surviving rows, as we have no rows in our fact table that relate to the Margin line name in our Dim table.
Step 4 accomplishes two things:
1. It accounts for the group order field that combines with our group name in our underlying Query. It is not enough to remove filters over just the group field, we must also include group order column as they are queried together. This is required in Power BI but not in Power Pivot, as Power Pivot uses MDX to query.
2. By removing filters only on these two columns (vs. the entire table), and keeping filters in place over the more granular detail lines, we avoid double reporting our subtotal on the group and line level on the matrix. Otherwise, our subtotals would duplicate all detail lines as reported above the subtotal row. This especially becomes an issue if your report readers export data into Excel.
This specific table expression FINORDER[Group Ord] <__maxline in this cascading example works dynamically the same for Margin as it works for Margin Less Payroll, except the range sets itself to capture everything less than Group Order 3.5 (See Margin Less Payroll, another subtotal line).
This method is ideal for subtotals that work over a sequential order like we’d find in 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.
Sum_Margin =
//Margin requires that I ignore the FINORDER and 1GL relationship as there is nothing to agg. through the relationship for the Margin subtotal line.
//Removing original filters allows the Margin label to appear as a group and line name.
//We also must REMOVEFILTERS over the sort order columns which is part of the query and report filter.
//Showing the amount at the line level also allows us to view conditional formatting.
CALCULATE (
[GL Amt],
FINORDER[Group] IN ( { "Sales", "Cost of Goods" } ),
REMOVEFILTERS (
FINORDER[Group Ord],
FINORDER[Line],
FINORDER[Order]
) -- REMOVEFILTER on Group Order is required as the query pairs Group name with the sort column.
)Since we are not filtering for a sequential range of dim row values, we can use a table filter FINORDER[Group] IN ( { “Sales”, “Cost of Goods” } ) 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 as a CALCULATE modifier to the 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 FINORDER table, but this time the order does not dictate the result of the expression.
Sum_Switch =
var __groupord = SELECTEDVALUE(FINORDER[Group Ord])
var __switch = SWITCH(__groupord,
2.5, [Sum_Margin],
6.5, [Sum_EBALOC],
8.5, [Sum_EAALOC],
[GL Amt])
return
__switchTo 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