Drill-Down & Up P&Ls in Tableau

May 5, 2020

Klaus Schulte

Drill-Down & Up P&Ls Tableau is the next part of a series of posts on visualizing financial data on this blog recently.

• Visualizing P&L Statements reflects the general use of visuals in the context of P&Ls and gives examples for bad and good practice.
• Adding Custom Sub-Totals in Tableau describes four solutions to combine normal aggregations and table calculations in one visualization and to define custom labels for sub-totals, a requirement also needed in the context of P&Ls.
• Multiple Drill-Downs / Drill-Ups leverages a new Drill-Down & Drill-Up technique introduced by Rosario to achieve an exploratory analysis across multiple hierarchies.

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.

1 Context

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.

2 How to do it

2.1 Data Modeling

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:

• Add a new dimension to the hierarchy structure with the names of the sub-totals on the highest account hierarchy level (e. g. Gross Profit, EBIT, etc.).
• Bring in a second copy of the data. Using two copies will allow us to customize the sub-total labels and amount calculations for each copy of the data independently.

2.2 Defining the Hierarchies

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.

2.3 Preparatory actions

STEP 0: MODELING THE DATA

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.

STEP 1: CREATE PARAMETERS

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

2.4 Definitions for the Drill-Down

STEP 2: DIMENSIONS FOR THE DRILL-DOWN

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.

• For copy 1 of the data, we will use Cluster, Position, Type or Account.
• For copy 2 of the data, we will use: Subtotal, Cluster, Position or Type.

“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.

2.4 Definitions for the Drill-Down

STEP 3: CALCULATE THE DIMENSION TO PRESENT THE DRILL UP MENU OPTIONS

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”.

STEP 4: CALCULATE DIMENSIONS THAT WILL SUPPORT US WITH THE DRILL UP

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]

2.6 CALCULATE AMOUNTS

STEP 5: CALCULATE THE VALUES OF CURRENT YEAR, PREVIOUS YEAR AND THE DIFFERENCE BETWEEN BOTH AMOUNTS

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

2.7 CREATE VIEWS

STEP 6: CREATE A GANTT GRAPH AS THE MAIN GRAPH

Apply data filtering:

• Filter        ⬅   DD_Filer = TRUE

Move to the shelves of:

• Columns ⬅ New Calculation “BRIDGE” and Bridge_Axis
• Rows ⬅ “DD Label Group” ordered by the “No” field and header is hidden
• Rows ⬅ “Copy” header is hidden
• Rows ⬅ “DD Label” ordered by the “No” field
• Rows ⬅ “PY YTD” and “CY YTD”

Move to the shelves of:

• Color ⬅ Color Waterfall
• Size ⬅ -Bridge
• Detail ⬅ DD Level, DD Path, Reference Line
• Tooltips ⬅ DD Path (Colored) & DD Path (gray)
• Text ⬅ Bridge

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.

STEP 7. CREATE THE SHAPE GRAPH FOR THE DRILL-UP.

Apply data filtering:

• Filter ⬅ DU Filter = TRUE

Move to the shelves of:

• Columns ⬅ DU Label and make it continuous
• Detail ⬅ DU Path
• Detail ⬅ DU Value
• Color ⬅ DU Color
• Tooltips ⬅ DD Label
• Text ⬅ DU Label – Path – Find Last
• Text ⬅ DU Label – Value – Find Last

Use the shape mark and select an arrow shape.

2.8 CREATE THE DASHBOARD & DEFINE PARAMETER ACTIONS

STEP 9: PARAMETER ACTIONS

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

3 Wrap Up

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)!