Written by: Robert Crocker, Rosario Gauna, and Klaus Schulte.

Download data here

Visualizing financial data has been quite a topic 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.

gif_PL

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

hierachyAccount hierarchy + top level sub-total

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.

hierarchy2

Finally, the formulas will also be adjusted so that the Drill-Down parameter action only fires when a Copy 1 item of the data is selected. Selecting a Copy 2 Drill Down level will not change.

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 Path LEFT([Path], FINDNTH([Path] + “/”, “/”, [DU Level] + 1) – 1
DU Label SPLIT([DU Path], “/”, -1)

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 }
Variance in % [DU Label – Value – Find Last]/[Total variance]
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.

chart

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
  • Text Variance in %

Use the shape mark and select an arrow shape.

pl_bc

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 heirachies within any relevant contexts, 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)!