Jul 30, 2022
How to if Grand Total calculation is different in Tableau?
Have you come across a scenario where your calculation in grand total may be slightly different than that specified in the rows? Let me give you a context. And for that let’s consider our good ol’ Tableau Superstore dataset.
With this dataset we will use a column ‘Product Name’ that can be sorted on Profit. This column can further be sorted on three values — Top N, Bottom N, and All — where the user selected variable ’N’ is the number of sorted rows to be made visible, for example, Top 10, Bottom 20 and so on.
(Here, I will assume that the reader is aware of how to use sets to sort and show numbers of rows entered in the parameters. If not, then refer the workbook link given towards the end of the blog.)
Furthermore, for every Product Name row displayed, the requirement is to show ‘Percentage Profit Contribution’ of the product, first, among the selected N products; and second, among all the products in the dataset.
For those not in the know, Percentage Profit Contribution for a product is assessed by dividing the product’s Profit by total profit of the products selected or are in the dataset.
When I select ‘All’ rows the grand total will be 100%. But when Top/Bottom is selected then the grand total will show the % Profit Contribution of Products (records) visible as specified in ’N’ parameter. Let’s look at the 2 methods of how we can achieve this.
First, find the %Profit Contribution of the Products (records) selected to be visible. Name this calculation as ‘%Profit Contribution in Sort Universe’. This calculation will be used in further calculations.
Method 1
The first method is where we use MIN and MAX functions. I prefer this as this is an easy method. But this method has an issue which will be covered later in this blog. Create the following calculated field named ‘% Profit Contribution (Min Max).
Method 2
This second method is using the Size function. In the detail mark place a calculation field named ‘Size’ with the following text: SIZE() .
Here do ensure 2 things. Place all the dimensions on the sheet that needs to be considered for calculating the ‘%Profit Contribution’ before you place the ‘Size’ field on the sheet. Then, right click on the calculation field ‘Size’ and select ‘Edit Table Calculation’. In this case it will be Table (Down).
But what you select among all the option visible in the above dialog box depends on the scenario. There are requirements where there is a Row Grand Total and ‘Table Across’ needs to be selected. Or there is a need to perform row grand total followed by the column total for which option ‘Table (across then down)’ needs to be selected. You may even opt for the Specific Dimensions in a particular order in which you want your calculation to happen. So, in short, assess the scenario, place the dimension in rows/columns bar and then select the option accordingly.
Once you finish this step, check the value of ‘Size’ appearing in the grand total column and use this value in the calculation below. Now create a field named ‘% Profit Contribution (Size)’ with the following text.
After you place this calculated field on the sheet, right-click on the calculation field and select ‘Edit Table Calculation’ and perform the same setting done for the ‘Size’ calculation field above.
To understand this holistically, bring one more column of Profit, right-click and select percent of total under quick table calculation. You will notice that the grand total will always be 100% as it will be the sum of % Profit Contribution of all products visible. We have no use of this in our conversation further except to put things to perspective and view the comparison.
So, this brings to the end of the steps to be taken but one does wonder what is the issue with min max function.
Issue using Min Max function
When we enter 1 in the ’N’ parameter, then the grand total in the column where we are using the MIN MAX function appears wrong. So, to avoid this, restrict the Minimum Value of ’N’ parameter to 2.
I would recommend visiting this link, to understand it better. I have put this together in a dashboard where you can test this out or can even download the workbook. Till next time. Ciao.
