Calculated field in Grand Total in Tableau (Pic Credit: Author)

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.

Label Change of Grand Totals (Pic Credit: Author)

I’ve changed to Profit, Sales & Profit Ratio as shown in the image below. The initial layout will look like this.

Initial Layout (Pic Credit: Author)

1. Format Settings

Create a calculated field named ‘Profit Ratio’.

SUM([Profit])/SUM([Sales])

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.

Removing data from rows (Pic Credit: Author)

However, in Grand Total -> Numbers, select Custom and remove the ‘@’ sign. Then select Percentage and specify the decimal places.

Formatting Grand Total Value (Pic Credit: Author)

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:

Profit Ratio Calculation for MIN MAX Function (Pic Credit: Author)

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.

SIZE Function Table Calculation Setting (Pic Credit: Author)

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)’.

Profit Ratio Calculation for SIZE function (Pic Credit: Author)

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.