Drill-Down & Up P&Ls in Tableau
May 5, 2020
Klaus SchulteDrill-Down & Up P&Ls Tableau is the next part of a series of posts on visualizing financial data on this blog recently.
Against this background this post is building up on the previous posts and presents a Profit & Lost dashboard with custom Subtotals and with the use of Drill-Down & Drill Up, to enable an exploratory analysis within a typical account hierarchy.
This is a joint work of Robert Crocker, who raised the need for this from a customer project, and Rosario and Klaus.
Finance is the close to the last industry I (Robert) would’ve imagined working in, but here I am. For the past 7 months I have been happily contracting with a Financial firm.
At first blush, I knew these Excel wizards were aware of things I couldn’t perceive from their tabular data. Only after spending months productively procrastinating by refining the design of existing dashboards could I face the overwhelming collection of tables these analyst live in day-to-day.
Coincidently, that’s when Klaus started sharing these invaluable posts focused on adding value to my audience without striping them from their tables. Turns out tables have a uniquely valuable form in the context of the P&L. Unfortunately, Tableau falls short in providing us with the all we need to present tabular data.
Thankfully, hackers like Klaus and Rosario are masterful enough of both Tableau and Finance to add the function this tabular form lacks. But we can do more (we can always do more) than custom subtotals and a looping drill path. We can bump up the style to reflect the modern UI that’s so “on-trend” in the Tableau community by adding a navigable breadcrumb trail.
In a Profit & Lost Statement, we believe it’s crucial to customize the labels of the sub-totals to a company’s naming conventions. To do this within the view, we have to leverage the first solution of the Adding Custom Sub-Totals in Tableau post: Custom Sub-Totals with Data Densification.
Thus, we have to model the data first:
In Rosario’s original blog, the Drill-Down menu is handled independently of the main graph(s). Instead, in this example, the Drill-Down is applied directly to the main graph. For these cases, we can get rid of the last level of the Drill-Down and use only the 4 levels from the hierarchy.
Like described in the Adding Custom Sub-Totals in Tableau post, each copy will handle a different level of the hierarchy. For the more granular hierarchy level, the first copy of the data we will use the SUM(), and for the other copy of the data the RUNNING_SUM() function will be required for the sub-totals.
Finally, the formulas will also be adjusted so that the Drill-Down parametr action only fires when a Copy 1 item of the data is selected. Selecting a Copy 2 item will not change the Drill-Down level.
When bringing in a second copy of the data, the “Table Name” field is automatically created to help us identify the data copy in question.
Although it’s possible to directly use the “Table Name” field for calculations used to identify the copy of the data being worked on, we will create a new dimension “Copy” to be used in our formulas to identify the copy of the data.
Copy | CASE [Table Name] WHEN “Sample” THEN 1 WHEN “Sample1” THEN 2 END |
Convert the “Copy” field to dimension with the right click.
For this technique it only needs the use of two parameters. A third parameter has to be added to store the Values for the breadcrumbs in the drill-up section.
PARAMETER | TYPE | CURRENT VALUE | ALLOWABLE VALUES |
Level | Integer | 0 | All |
Path | String | Net Income | All |
Breadcrumb Value | String | 0 | All |
The dimensions “DD Level” and “DD Path” will be used to update the parameters defined in the first step.
The value of the “DD Label” dimension will contain the legend visible to users and will be determined by the displayed copy of the data and the current Drill-Down level.
“DD Label Group” will help us to group the values of “DD Label” according to the group they belong to. Also, “DD Path (colored)” and “DD Path (grey)” split up the path parameter to more clearly differentiate between the current level and prior levels. “DD Value” will be needed to push the selected value to the Breadcrumb Value parameter.
DD Level | IIF([Level] = 3, 0, IIF([Copy] = 1, [Level] + 1, [Level])) |
DD Label Group | CASE [Level] WHEN 0 THEN [Subtotal] WHEN 1 THEN [Cluster] WHEN 2 THEN [Position] WHEN 3 THEN [Type] END |
DD Label | IIF([Copy] = 2, [DD Label Group], CASE [Level] WHEN 0 THEN [Cluster] WHEN 1 THEN [Position] WHEN 2 THEN [Type] WHEN 3 THEN STR([Account]) END) |
DD Path | IIF([Level] = 3, “Top Level”, [Path] + IIF([Copy] = 1, “/” + [DD Label], “”)) |
DD Path (colored) | SPLIT ([Path], “/”, -1) |
DD Path (grey) | IFNULL(LEFT ([Path], LEN ([Path]) – LEN ([DD Path (colored)])),””) |
DD Value | IF [Level] = 3 THEN ‘0’ ELSEIF MIN([Copy])=2 THEN [Breadcrumb Value] ELSE [Breadcrumb Value] + “/” + STR(ROUND(SUM([CY Ytd])-SUM([PY Ytd]))) END |
Convert the “DD Level” field to a dimension.
The following definition is to select and/or filter from records that meet the Drill Down user’s selections set to the value of the Path.
DD Filter | [Level] = 0 OR CONTAINS([Path], [DD Label Group]) |
This formula is a simplified version of the one used in the original blog. This was possible, since the descriptions used in the account hierarchy are not repeated nor are they contained within the descriptions of the rest of the values. If these conditions aren’t met, the original formula should be used.
Four Drill Up levels will be handled: “Net Income”, Cluster, Position and Type. Since we don’t have such values in the data of a dimension, we apply a small trick, which is to create the new dimension from another field, even though there is no relationship between the new dimension created and the original dimension. In this exercise we will use the Account dimension to create the new dimension.
DU Level | [Account] % 4 |
Convert “DU Level” to a dimension with the right click.
The formula described here calculates the remainder of dividing the numerical value of the “Account” field by 4, giving as possible values numbers between 0 and 3. That is, we are arbitrarily grouping the data into four groups (0, 1, 2, 3) , one for each of the required Drill-Up menu options.
This technique is known as “Internal Data Densification”.
Once the “DU Level” dimension has been defined under the Internal Data Densification technique, we will calculate the rest of the dimensions required for the Drill-Up.
Each of the new dimensions will be determined by: The value of the “DU Level” dimension and by the values of the parameters, whose value is determined by the decisions made by the user.
DU Label | CASE [DU Level] WHEN 0 THEN “Top Level” WHEN 1 THEN SPLIT([Path], “/”,2) WHEN 2 THEN SPLIT([Path], “/”,3) WHEN 3 THEN SPLIT([Path], “/”,4) END |
DU Path | “Net Income”+ IIF([DU Level]>= 1, “/” + SPLIT([Path], “/”, 2), “”) + IIF([DU Level]>= 2, “/” + SPLIT([Path], “/”, 3), “”) + IIF([DU Level]>= 3, “/” + SPLIT([Path], “/”, 4), “”) |
The “DU Level” and “DU Path” dimensions will be used to update the parameters defined in step 1. The “DU Label” dimension will contain the legend that will be used in the tooltips to identify the level of the Drill-Up.
For the breadcrumb shape graph, we can identify the current item from the path with “DU Label – Path – Find Last”. The current value is pushed to the Breadcrumb Value parameter with the DU Value field and taken back from the parameter with the “DU Label – Value – Find Last” field.
DU Label – Path – Find Last | IF LEN(TRIM(RIGHT([DU Path], LEN([DU Path]) – LEN(REGEXP_REPLACE([DU Path], ‘[^/].$’, ”))))) > 30 THEN LEFT(TRIM(RIGHT([DU Path], LEN([DU Path]) – LEN(REGEXP_REPLACE([DU Path], ‘[^/].$’, ”)))), 30) + ‘…’ ELSE TRIM(RIGHT([DU Path], LEN([DU Path]) – LEN(REGEXP_REPLACE([DU Path], ‘[^/]*.$’, ”)))) END |
DU Value | STR(ROUND([Total variance])) + IF [DU Level] >= 1 THEN ‘/’ + SPLIT([Breadcrumb Value], ‘/’, 2) ELSE ” END + IF [DU Level] >= 2 THEN ‘/’ + SPLIT([Breadcrumb Value], ‘/’, 3) ELSE ” END + IF [DU Level] >= 3 THEN ‘/’ + SPLIT([Breadcrumb Value], ‘/’, 4) ELSE ” END + IF [DU Level] >= 4 THEN ‘/’ + SPLIT([Breadcrumb Value], ‘/’, 5) ELSE ” END |
DU Label – Value – Find Last | INT(TRIM(RIGHT([DU Value], LEN([DU Value]) – LEN(REGEXP_REPLACE([DU Value], ‘[^/]*.$’, ”))))) |
To finish the definitions for the Drill Up, a filter is required to select the levels of the Drill Up menu that will be visible. DU Color will allow us to distinguish the last used level, from the higher levels in the hierarchy.
DU Filter | [DU Level] <= [Level] |
DU Color | [DU Level] = [Level] |
To define the formulas, we must keep in mind the copy of the data with which we are working. And be careful not to duplicate the amounts of the two copies of the data.
A reference line will be used to bring space between the header and the visual in our tabular view.
CY YTD | CASE MIN([Copy]) WHEN 1 THEN SUM([CY Ytd]) WHEN 2 THEN RUNNING_SUM(SUM([CY Ytd])) / 2 END |
PY YTD | CASE MIN([Copy]) WHEN 1 THEN SUM([PY Ytd]) WHEN 2 THEN RUNNING_SUM(SUM([PY Ytd])) / 2 END |
Bridge | [CY YTD] – [PY YTD] |
Bridge Axis | CASE MIN([Copy]) WHEN 1 THEN RUNNING_SUM([Bridge]) WHEN 2 THEN [Bridge] END |
Color Waterfall | IIF(MIN([Copy]) = 2, “grey”, IIF([Bridge] < 0, “red”, “blue”)) |
Total Variance | { (SUM([CY Ytd])-SUM([PY Ytd])) / 2 } |
Reference Line | WINDOW_MIN([Bridge]) * 1.5 |
Apply data filtering:
Move to the shelves of:
Move to the shelves of:
For the table calculations, adjust your computation to specific dimensions: DD Label Group, Copy, DD Label, DD Level and DD Path
Adjust the colors of the bars of the Waterfall.
Edit the Worksheet Text to show the <ATTR (DD Path (gray))> <ATTR (DD Path (colored))> fields and modify the colors to be used in each field.
Set Row Banding on Copy-Level. This allows the dashboard user to actually identify the sub-total in the main view.
Apply data filtering:
Move to the shelves of:
Use the shape mark and select an arrow shape.
After putting together the chart and the top menu on a dashboard, the following Parameter Actions will be defined for:
Drill-Down:
PARAMETER ACTION | SOURCE | TARGET PARAMETER | FIELD OR VALUE | AGGREGATION |
DD Level | Chart | Level | DD Level | Average |
DD Path | Chart | Path | DD Path | None |
DD Value | Chart | Breadcrumb Value | DD Value | None |
Drill-Up:
PARAMETER ACTION | SOURCE | TARGET PARAMETER | FIELD OR VALUE | AGGREGATION |
DU Level | Top Menu | Level | DU Level | Average |
DU Path | Top Menu | Path | DU Path | None |
DU Value | Top Menu | Breadcrumb Value | DU Value | None |
Armed with the knowledge shared up to this point you can create fully navigable hierarchies within any relevant context, not only finance. We believe this is going to be a step change in the dashboard design and functionality coming from our beloved Tableau Community.
This approach could replace Only Relevant Values, boosting performance. Keep your audience focused on only the most meaningful information, greatly increasing the insightfulness of your dashboards.
We have purposely kept the style simple, but for those creatively curious among you be sure to peek at my Breadcrumb Collection on Dribbble. Can’t wait to see what you all create!
That’s it! We hope you enjoyed reading and find own use cases for this. Find & download the workbook on Tableau Public here.
If you have any questions, reach out to us on Twitter (@ProfDrKSchulte, @rosariogaunag & @robcrock)!
3 COMMENTS
benedictemai says:
thank you to have taken time to reply. 🙂
benedictemai says:
Hi, your work is very interesting and amazing to read some P&L’s.
I have a small question about the structure : why do you repeat gross margin at the top ? It would be better to have Revenue and Cost of Sales…
A french controller
Klaus Schulte says:
Hi Benedict, my first hierarchy level is an aggregation of cost/income types. I have revenue and cost of sales on the next level. If I bring revenue and cost of sales here, I would have to repeat it on the next level. I could have named this bucket differently, like ‘Revenue & Cost of Sales’ (which is the Gross profit 🤷♂️) 🙂