3/10/2020
A Journey to Customized Subtotals with Desk Calculations
This previous weekend Tableau Zen Grasp Klaus Schulte printed an ideal weblog put up displaying 4 alternative ways of Adding Custom Sub-Totals in Tableau. On this weblog put up, he demonstrates how to do that with knowledge densification and desk calculations. I posted my very own answer later that night, which I’ll describe under, however I needed to debate the journey to get to the answer.
We regularly see completed visualizations or technical weblog posts referring to Tableau, however once we see the tip consequence, we do not see the hours of iteration that it will probably take to get there. These iterations can embody useless ends, errors and generally overlooking the only of options. So in an effort to point out this course of extra, I assumed I’d stroll by the method that I took. Take into account that I did not report each step alongside the best way, however I’ll spotlight my course of as greatest I can.
As well as, I’d level out that we regularly discover these options by neighborhood work and collaboration. Had Klaus not posted this weblog put up, which references work by Rody Zakovich, then I certainly would not be penning this put up. As with many Tableau methods, we construct on the work of many others that come earlier than us.
Once I learn Klaus’s weblog put up, I suspected there was a better means to do that with tableau calculations. I am unable to pinpoint precisely why I assumed this, however there have been an excellent many steps in his course of and a few lengthy formulation. So I obtain Klaus’s workbook to see precisely what he did with the Tableau Desk calculations.
Let’s begin with the purpose. That is what Klaus created. A desk that reveals the person values for a subcategory, however as an alternative of the standard subtotals, he has a working sum for the subtotals for every class. That is a kind of issues that may take 30 seconds in Excel, however as we’ll see, there is a bit extra concerned in Tableau.
Utilizing the Superstore knowledge, the very first thing I did was create a primary desk with Class and Subcategory after which I added all subtotals from the evaluation tab. My fast thought was to place an index() operate on this desk in order that I may see what was happening with these subtotals. Discover that the subcategories and classes every have their very own sequence down the desk. Not surprising.
My subsequent collection of steps have been based mostly on this logic; « if the quantity is within the Subcategory sequence then use the Sum([Sales]), in any other case use a Running_Sum(Sum([Sales])) ». After a couple of fast makes an attempt at a calculation round that logic, I made a decision so as to add a dimension() operate to the desk. Once I set Measurement to compute utilizing Subcategory it seems like this.
This was my first breadcrumb as a result of this isolates the items for every half, one sum and one working sum.
My subsequent step concerned a couple of calculations. First, I attempted a single calculation utilizing the logic I outlined above.
Calculated Area: New Calc
Method:
if dimension() = 1 then RUNNING_SUM(SUM([Sales]))
else sum([Sales])
finish
Whereas the logic would possibly seem to make sense, this was a failed try, as a result of irrespective of how I set Compute Utilizing or adjusted the desk calculation, it did not remedy the issue. This formulation would not calculate the working sum as I want it. We’ll come again to this, as a result of this failure is definitely a step within the answer that comes again later.
My subsequent step was to interrupt the calculation into two elements in order that I may see what was happening for every a part of the calculation. I figured that if I may break them up them I may management them simpler for sum and working sum.
Calculated Area: New Calc 1
Method:
if dimension() = 1 then null
else sum([Sales])
finish
Calculated Area: New Calc 2
Method:
if dimension() = 1 then RUNNING_SUM(SUM([Sales]))
else null
finish
This New Calc 2 did not create a Running_Sum based mostly on my calculations, so I created yet another calculation to perform that piece.
Calculated Area: Operating Sum of New Calc 2
Method:
RUNNING_SUM([New Calc 2])
This is what all of those calculations look appear to be at this level.
This was my first glimmer of hope for the right consequence. In actual fact, it is a variation of the answer the place the values are in two separate columns. I would name {that a} win. Once I take away all the different columns that I created it seems like this.
I went again so as to add Sum([Sales]) again into the forumla instead of the NULL.
Calculated Area: New Calc 2
Method:
if dimension() = 1 then RUNNING_SUM(SUM([Sales]))
else Sum([Sales])
finish
At this level it seems like a step backwards. It is now the working sum down the whole desk. Not precisely what we’re on the lookout for.
Nevertheless, after I modified the « Compute Utilizing » to Class, I get this.
That is the second the place I defined, « Sure! » whereas making a fist, and my household watching TV checked out me humorous. Do not choose. That is thrilling stuff. These ARE the droids we have been on the lookout for.
My subsequent thought was to wash up a few of these calculations. Bear in mind again firstly when the Running_Sum of Gross sales calculation wasn’t getting the consequence I needed in New Calc. Effectively, if it is not working the best way I need, then why do I want it? So I eliminated Running_Sum from the equation.
Calculated Area: New Calc 2
Method:
if dimension() = 1 then Sum([Sales])
else Sum([Sales])
finish
After making that change I verified that calculation was nonetheless working as anticipated. Yep, nonetheless good. What was a street block early on within the course of and incorrect now helped me simplify the formulation.
If each circumstances of the IF assertion produce the identical consequence, Sum([Sales]), then I can simply use Sum([Sales]) into the Operating Sum Calculation and do that all with a single calculation. I did not want this IF calculation in any respect. It would not get a lot less complicated than this.
Calculated Area: Operating Sum of New Calc 2
Method:
RUNNING_SUM(Sum([Sales]))
I now have the consequence I used to be on the lookout for in a single calculation. And it makes full sense. It is quite simple. It computes the working sum on Class, so we get particular person values for the Subcategories and the Operating Sum for the Subtotals that are the Class Subtotals.
The subsequent step is coping with the label. Klaus’s workbook used this answer from Rody Zakovich.
Calculated Area: Label
Method:
IF [first]=0 AND [last] =0
THEN MIN(« Operating Complete after « +[Category])
ELSE MIN([Sub-Category])
END
I knew I may do that identical calculation with the dimensions() operate that I utilized in my earlier iterations. These earlier steps paid off once more.
Calculated Area: Label
Method:
IF dimension()=1
THEN MIN(« Operating Complete after « +[Category])
ELSE MIN([Sub-Category])
END
The important thing to this one is setting the Compute Utilizing to Subcategory for the Label, as an alternative of at Class like we did for the Operating Sum Calc. This answer permits me to take away First() and Final() and due to this fact Measure Names and Measure Values. I am down to 2 calculations, one for the numbers and one for the label. I adjusted the « spacer » calculation, and ended up doing it otherwise than Klaus did.
I additionally experimented with making the desk utilizing a dual-axis circle in order that I may leverage the labels of the circles and management alignment. This strategy eliminates the necessity for the spacer calculations.
The tip result’s a quite simple and stylish answer, however I hope by displaying you all of those steps that you’ll admire that I did not merely open up Klaus’s Tableau workbook and kind two easy calculations to realize this consequence. Whereas the answer appears quite simple whenever you see it, to finish up at this answer required a number of steps, a couple of missteps and a few foolish errors. In the end, I broke issues down, put them again collectively and labored by the logic. This took a while, nevertheless it was satisfying to unravel the issue on this means and I realized one thing from the method.
Beneath is a Tableau Public Visualization placing all of this collectively.
