Show/Hide data in Rows/Columns/Grand Total in Tableau
Have you ever come across any requirement where one calculated field should appear in rows/columns but not appear in grand total or vice-versa? Let’s dive directly into 3 ways we can do this.
Case: Requirement is to view the Profit and sales generated by Customers on each of the weekdays. Followed by the total profit, sales and profit ratio in the grand total. We will use the Sample superstore data of Tableau for this.
Place Order Date in the Columns bar. Right click and select WeekDay.
Drag and drop Customer Name in the Rows bar
Place Profit and Sales in the Text marks card.
Select ‘Show Row Grand Totals’ from Analysis -> Totals menu.
Right click on the Grand Total and select ‘Format’. Change the Label under Grand Totals.
I’ve changed to Profit, Sales & Profit Ratio as shown in the image below. The initial layout will look like this.
1. Format Settings
Create a calculated field named ‘Profit Ratio’.
Place this calculated field in the Text Marks Card.
You will see the Profit Ratio field as the third value everywhere. We need to restrict this to Grand Total. So, right click on the field and select Format. In Default -> Numbers -> Custom, enter ‘@’ sign.
However, in Grand Total -> Numbers, select Custom and remove the ‘@’ sign. Then select Percentage and specify the decimal places.
In case, you want to view this field in rows or columns but not in Grand Total then do the reverse:
Default -> Numbers -> Percentage and select the decimal places
Grand Total -> Numbers -> Custom and enter ‘@’ sign.
2. MIN..MAX Function
Create a calculated field named ‘Show Grand Total (MIN..MAX) and enter the following:
Here by comparing MIN of the order date to MAX of the order date, we are assuring that the Profit Ratio calculation will appear in the grand total only. But, in case you want the calculation to appear in rows or columns and not grand total, use = sign instead of <>.
3. SIZE Function
Place Size() in the Detail Marks Card. Ensure this field appears in the tooltip.
Right Click and select Edit Table Calculations
Select Specific Dimensions and then select ‘WeekDay of Order Date’ only.
When you hover the mouse over the table, notice that when the mouse appears on the weekdays like Monday or Sunday the value of this SIZE() is 7 but in the grand total it becomes 1. We will use this value 1 in our calculation.
Create another calculated field named ‘Show Profit Ratio (SIZE)’.
Here, if the value of SIZE() is 1 then the Profit Ratio calculation should appear.
So, place this calculated field in the Text Marks Card.
Right click and select ‘Edit Table Calculation’ and do the same thing we did with the SIZE() table calculation. Select ‘Specific Dimension’ and select ‘WeekDay of Order Date’.
So, the Profit Ratio calculation will appear only in the grand total. But if you wish to have a profit ratio in the rows then specify the value of SIZE() as equal to 7. And this causes the value to appear in rows and not in grand total.
Until recently, I was not aware of the first method which according to me is the easiest of them all, hence this blog. Do share if you have other ways. For now, that’s all. Merci.