On Twitter today, Brit Cava asked whether it was possible to use a parameter to control how a table calculation computes (e.g. table across vs. table down). In addition, she wanted to know if it was possible to have both a % of total measure plus a normal sales volume measure inside of the same dynamic field (i.e. different numeric formats in one field). And naturally, this all had to happen in a table. We both seem to live in a world where we’re constantly having to sate people’s desires to meld Excel and Tableau…
Initially I wasn’t sure if this could be done, but I’d had three cups of coffee by then and allowed my caffeinated curiosity to checkmate my doubt. Lo and behold, this multi-format, cross-dimensional table calc wizardry is quite easy to achieve. You just need to set up a few calculated fields to make it work.
Setting up the Calculations
In this example we want to calculate the % of total Sales, but be able to select whether we want that % of total to be calculated across category for each region (i.e. how much of each region’s sales does each category represent) or across region for each category (i.e. how much of each category’s sales does each region represent).
Since we can’t dynamically change an individual measure field’s “compute using” via a parameter, we need to set up two distinct % of total Sales measures, set the “compute using” independently for each, and then use the parameter to toggle between them.
So let’s first create a % of Total 1 field (as shown below), and then just duplicate it and give the second one the name of % of Total 2.
Next we need to create a parameter. Since we’re going to want to be able to select Sum of Sales in addition to the two % of Total calculations, we need to set up the parameter with three choices:
1. % of Total – Compute Across
2. % of Total – Compute Down
3. Sum of Sales
We then create a third measure called Table Measure (my naming choices are highly creative), which selects the appropriate measure based on the parameter. If we were just going between the two % of Total measures, we wouldn’t need to add the “*100” next to each of those. But since the two percent measures are going to be in the same field as the Sum of Sales (a whole number), we don’t want to deal with decimal places for the Sales figures (e.g. we want 124,500 instead of 124,500.00). So we multiply the % measures by 100 to turn a .34 (34%) into a 34. We’ll deal with the “%” sign in a moment.
Setting up the Table
To create the table just drag the two dimensions we need for our analysis (in this case Region and Category) and place one on Columns and one on Rows. Then place the Table Measure field onto the Text shelf. If it isn’t already formatted, go ahead and format the measure to remove all decimal places.
Then select Edit Table Calculation for the Table Measure pill (right-click on the pill and select Edit Table Calculation). Here you will set the Compute Using for % of Total 1 to “Table Across” and the Compute Using for % of Total 2 to “Table Down”. The order doesn’t matter (i.e. 1 could be Table Down and 2 could be Across) so long as each one has a separate Compute Using setting.
At this point we can add the Toggle parameter to the worksheet and just toggle through the choices, watching the values change in the table. The one thing that is missing is that there is no % sign next to the % of Total values (e.g. 34% is displayed as just 34).
To address that we need one more calculated field, one that returns a “%” sign only when the parameter selection isn’t for “Sum of Sales” (i.e. choice 3); if Sum of Sales is chosen then this field simply returns Null.
Then place this new Format Label field onto the Text shelf along with Table Measure. Initially it will place this field beneath the existing Table Measure field. To move it, click on the Text shelf and select Edit Label (the three ellipses shown below). In the edit window, just move the Format Label field directly next to the Table Measure field and hit OK.
Now the % sign will show up next to the number when either % of Total choice is selected, but when the Sum of Sales is chosen it will be just a normal number with no decimal places.
I hope this proves helpful to you. You can download my sample workbook here.
Thanks for reading.
UPDATE: Joe Mako suggested another way of achieving the same goal, and as is usual with Joe, his suggestion was more elegant and more flexible (and allows us to show the dollar sign for the sales measure). It’s a similar approach to the one I used in Situation 1 on my conditional formatting post, but leverages the parameter vs. the measure value to determine which one to show.
To make Joe’s approach work, we just need to modify the two % of Total calculated fields and then set up a separate Sum of Sales field. In all three cases, we only show the result of the calculation if the relevant parameter choice is selected; otherwise, the calculation returns null.
Then these three measures are placed on the Text shelf. As before, Tableau will place these three measures on top of each other by default, but we can change that by editing the Text label and placing them side-by-side. This way only one of them will ever show.