Tableau Calculated Field Aggregate SUM for Lowest Level & MIN for All Others

Question

I am having a difficult time aggregating the lowest level of a row group with SUM while aggregating all higher row headers by MIN.

My data is like this structured in a group like this:

Assembly

Parent

Child

Orders

Inventory

And an example of my dataset might look like:

Assembly | Parent | Child | Supply Type | Available Quantity
A1       | P1     | C1    | Orders      | 0
A1       | P1     | C1    | Inventory   | 50
A1       | P1     | C2    | Orders      | 100
A1       | P1     | C2    | Inventory   | 0
A1       | P2     | C3    | Orders      | 50
A1       | P2     | C3    | Inventory   | 100

I want to SUM the measures in the Supply Type rows when I collapse up to the Child row group, but then to show the MIN when I collapse to the Parent and Assembly row groups. For instance, if I collapse at the Child level I want to SUM and have C1 to show a total of 50 and C2 to show a total of 100, but when I collapse at the Parent level I want to see the MIN and have P1 show 50 and P2 show 150.

I was able to partially achieve what I am trying to do with:

{ INCLUDE [Child],[Parent],[Assembly] : MIN({ EXCLUDE [Supply Type] : SUM([Available Quantity])})}

But now at the lowest levels the Order and Inventory measures show the same numbers (which is not accurate) since the EXCLUDE function is essentially ignoring the categories at the Supply Type row level.

You may receive a faster reply if you can show your current, non-working code. — halfer
When you say “collapse”, do you mean that you are placing your dimension fields into a hierarchy? (So you can say press + or - on the viz to expand or collapse the level of detail). If so, what is the order of the fields in your hierarchy? — Alex Blakemore
@AlexBlakemore yes, I have a hierarchy that goes Assembly > Parent > Child > Supply Type. I am collapsing/expanding the level of detail all the way from Supply Type (Inventory and Orders) up to Assembly. — CJK

Alex Blakemore Alex Blakemore · Accepted Answer · 2019-12-17T15:30:00

Ok, briefly, my recommendation is to (slightly) alter your approach. There are ways to literally do what you say you want, but they are more complicated and brittle than I think most people would want, just for the convenience of controlling by hitting + or - to expand and collapse levels (not to mention a bit confusing to the user). Especially when there are alternatives that are simpler and likely more reliable.

Here is one alternative method using parameters and calculated fields -- which is a pretty general approach for many things in Tableau.

Define a parameter that specifies how many levels of your dimension hierarchy you wish to display. Call it, say, Display_Level or whatever makes sense to you. Give it the data type string, and provide a list of allowable values, say "Assembly", "Parent" etc.
Show your parameter control and choose how you wish it to look, say a combo box or radio buttons.
Define one calculated field to be the displayed dimension as follows, and put on the Rows shelf or others as desired.

case [Display_Level] when "Assembly" then [Assembly] when "Parent" then [Assembly] + " - " + [Parent] when "Child" then [Assembly] + " - " + [Parent] + " - " + [Child] ... end

Define another calculated field to be the displayed measure as follows, and put on the Columns shelf or others as desired.

case [Display_Level] when "Assembly" then Min([Quantity]) when "Parent" then Min([Quantity]) when "Child" then SUM([Quantity]) ... end

This first draft may not be exactly what you want, but the approach can easily be extended. You are just using a parameter to control the display detail instead of clicking on the + or - next to a field name.

If you don't like the full names on the dimensions separated by dashes, then you can create multiple dimension fields to serve as labels, one per level, and have them either return the corresponding field or null depending on the parameter setting. For example, the Parent Label field could be defined as if [Display_Level] <> “Assembly” then [Parent] end Do something similar for the others. Then put all your “Label” fields on the viz to create row or column headers as desired. This will behave much like expand/collapse except that you control the detail using the parameter. There are several ways to get the Null to not display.

If your measure calculations are more complex than MIN() or SUM() - say if they use LOD calcs to rollup values first - then just modify your measure calculation.

Tableau Calculated Field Aggregate SUM for Lowest Level & MIN for All Others

1 Answers