It can sometimes be useful to have a dynamic crosstab or table where you can switch between dimensions and table calculations. The only problem is that the table calculation can then get confused. For example, if you want to see the percentage added horizontally(across/over the line) or vertically(down/over the column), two table calculations are needed. "Can you actually switch between measures as table calculations in Tableau?" So I asked myself.
Now I found out from a customer that this can be solved in Tableau simply with a parameter. The customer wanted the user to be able to choose the dimensions for row/line and column/column. The idea was that different qualitative properties could be set against each other. To top it off, with a LOD it is also possible to take into account something like 'percentage of the whole' . Below you can see the final result, below I will explain how that works:
Switching between dimensions
First, you create a parameter to switch between dimensions and a corresponding calc. You set the calculation to rows.
Then repeat the above step to create the same parameter and calculation for vertical or columns. Then set C_Horizontal to rows and C_Vertical to columns, also set the corresponding parameters to visible.
Switching between measures as table calculations
This step may sometimes not work, the point is to drag the measures from the marks pane into the calculation. That way Tableau preserves the settings for direction of the table calculation(across or down). I myself had to do this a few times when creating this blog because I very stubbornly just wrote down the calculation instead of dragging. (If you don't know what a marks pane is, that's the block between the data and the canvas, under the 'Filters' block).
Making the switch between table calculations percentage and LOD requires some parameters and calculations. First, the calculation for 'Sales as %' as below:
I duplicated this base as 'C_Sales as % (across)' and 'C_Sales as % (down)'. The changes only take place in the Marks Pane. Drag 'C_Sales as % (across)' into the marks pane, then under 'Compute Using: Table (across)' select it. For 'C_Sales as % (down)', repeat this step but select 'Compute Using: Table (down)'.
Next, create the calculation below for the LOD:
Now you can create the parameter and calculation to switch between measures, see below. First you create the parameter and then the calculation. CAUTION. You create the calculation by dragging the "pills" or measures into the calculation field, not by typing them:
Should it not work, it may be useful to rewrite the calculations for across and down. Then bring them back into the view and see if they calculate across/down. (And repeat the dragging of the pills as well).
It should eventually look like this.
Eventually, the worksheet should look like the image below. At the beginning of this blog you could already see the dashboard, through this link you can find it on Tableau Public. If you want to learn more about table calculations this blog by my colleague Jessica is also recommended.
