Tableau provides us the ability to display totals/grand totals for a measure. But do you know that you can override the default aggregations provided and display a completely different value?
Let’s look at this data where we have the total quantity by year and month.
To view the year totals across all months and/or month totals across all years, we would enable the totals as needed.
We now have the totals of the quantity for each year.
If we want to view the average quantity for each year rather than the sum, we can change how the totals are computed and get the desired output.
We now have the average of the quantity across all months for each year in the grand totals.
But what if we want to view a total value which is not among the options (Sum, Average, Minimum, and Maximum) provided? This can be achieved using calculated fields.
For example, let us see how we can view the total change between the first and last months for each year in the totals.
We can start with a calculated field that gets us the total quantity for each year and month and view it on the data pane along with the quantity measure.
We see that we have the same values for each year and month except for the totals. The values show up only when the distinct count of a month in a column is one, and do not show up in the total column since there is more than one month of data.
We can now modify the calculation to compute the total change and display.
We can now remove the original quantity measure to get the final output as desired. We can also change the “Grand Total” header to something more meaningful as “Total Change.”
We now have the total quantity by year and month, and the total change in quantity from the first and last months of each year.
We use three kinds of cookies on our websites: required, functional, and advertising. You can choose whether functional and advertising cookies apply. Click on the different cookie categories to find out more about each category and to change the default settings.
Privacy Statement
Required cookies are necessary for basic website functionality. Some examples include: session cookies needed to transmit the website, authentication cookies, and security cookies.
Functional cookies enhance functions, performance, and services on the website. Some examples include: cookies used to analyze site traffic, cookies used for market research, and cookies used to display advertising that is not directed to a particular individual.
Advertising cookies track activity across websites in order to understand a viewer’s interests, and direct them specific marketing. Some examples include: cookies used for remarketing, or interest-based advertising.