Sign in or start a free trial to avail of this feature.
3. Quick Table Calculations Part 1
Tableau has some great in-built formulas that can be accessed from the shelf. In this lesson, we'll go through how to use some of these formulas such as Running Total and Percent of Total.
To explore more Kubicle data literacy subjects, please refer to our full library.
Quick table calculations
- Represent pre-built formulas that Tableau has prepared for you
- Accessible from the Pill dropdown menu in the shelf
- Used in almost every dashboard, but some calculations are not very well understood by users
Calculations used in this lesson
- Running Total: Cumulative sum of value over time
- Difference: The difference between the current time period and the previous time period
- Percent Difference: The percent difference between the current time period and the previous time period
- Percent of Total: The percent of total value that the current data point represents
In this lesson, we are going to learn how to use quick table calculations in Tableau.
Quick table calculations are pre-built formulas that are typically very useful for you when building your visualizations.
However, when you are using quick table calculations it's necessary that you understand exactly how the quick table calculation works.
A sum of these are not very straightforward In the current sheet, I have month on the x-axis and monthly sales on y-axis for all products From the chart, you can see that monthly sales vary between about 10,000 a month and 35,000 per month If I wanted to show a running sum showing cumulative sales growing over time, I can hit the dropdown go to quick table calculation, and select the running total And this charts cumulatively all of the sales each month over the time period If I hit the drop down again and go to quick table calculation I'll see that the next option is difference.
And difference calculates the change in sales between the current month and the previous month In this chart, I can see that at this data point the sales in January were just under 4000 pounds less than sales in December.
And sales in December were 3,000 pounds greater than sales in November.
By default, the difference calculation compares current month's sales with the previous month's sales but you can adjust this if you wish.
Hitting the dropdown, you can see that there's a Relative to option and we can say, relative to the first month and this is all relative to the first month in January 2012 or we could say relative to the next month.
We can adjust the formula to pick any relative month by double clicking and accessing the formula in here Next up in our quick table calculations is the percent difference And this will be very similar to the previous chart except it shows the change as a percentage change rather than an absolute change in sales. Again, you can adjust this particular calculation relative to next month, first month, previous, or last After percent difference, we have percent of total which I tend to use very often Percent of total doesn't work very well when we only have a single line in our view because it simply splits each monthly sales as a percentage of the total sales over the time period which is not very intuitive.
So, this line simply tells me that in February 2014 we had 1.34% sales for the total time period.
Instead, I'd like to use this calculation to give me market share for the different products over time.
To do this, I'll grab the products and drag in to color.
I can't yet see market share, but I am nearly there.
The sheet is still calculating, the percent based on the total sales in the whole sheet But I only want to see market share for each month.
And so, to do this, I'll hit the drop down and compute using table down. And this now tells me the market share for each product each month.
So Sycalone had market share of 35.64% in January 2012 but later on, its market share dropped to 10.57% in June 2015.
When using the percent of total calculation like this it's very important to sense check your numbers because Tableau will often make assumptions in the background about how you want to calculate percent of total, which maybe different to what you intuitively want.
The easiest way to make this change is by compute using and testing the various options But you may also want to perform some hand calculations as well to back up these particular numbers In the next lesson, we will continue our analysis of quick table calculations examining rank, percentile, moving average and compound growth rate.