Let’s Math It Out in Tableau!

Data

Let’s Math It Out in Tableau!

by Katie Wagner

We’ve kicked off 2015 with answers to some of our most commonly asked questions. Another problem that appears involves a simple piece of functionality – totals. 

We know that Tableau can create totals for us through the Analysis menu. In Tableau 8.1, a new feature called Two Pass Totals was released. These totals can be calculated as a sum, average, minimum, or maximum by right-clicking on the measure and selecting Total All Using.  Our goal here is to understand how this feature works by crunching out the numbers in a variety of ways.

We’re going to focus on one product, Geographics Note Cards, and see what happens when we change the aggregation of our measure (Sales) and the way totals are calculated.

The default view shown gives us the total sales for both regions by selecting Analysis > Totals > Show Row Grand Totals. Using Tableau’s default Total All Using – Automatic, the same value would be true if we chose to Total All Using – Sum.

Total All Using 

Measure: SUM(Sales)
Total All Using – Automatic (or Sum)

Automatic (or Sum)

$123.84 + $47.03 = $170.87

Measure: SUM(Sales)
Total All Using – Average

What happens if I change the total computed as an average? The result is a visible average – adding up the values in the view and dividing them by the number of values.

Average 

$123.84 + $47.03 = $170.87

/2 = $85.44

Measure: AVG(Sales)
Total All Using – Sum

This time, instead of changing the way totals are calculated, what happens if I change the aggregation level of the measure?  Our result is simply a total of the averages: the average of Central sales + the average of West sales.

Sum 

$61.92 + $47.03 = $108.95

Measure: SUM(Sales)
Total All Using – Average

However, what happens if I compute the total as an average as well? The result is an average of the averages.

Average 

$61.92 + $47.03 = $108.95

/2 = $54.48

Finding the Solution for Total All Using – Automatic

These are all pretty straightforward, but a problem appears when we Total All Using – Automatic. The results aren’t the same as either of the previous selections.

To find our solution, we have to look at the underlying data instead of an aggregate level. Geographics Note Cards were purchased in the Central region on two days, 2/9/11 and 2/26/13. By adding the total sales from each of these orders to the sales from the West region, we see a total sales value of $170.87. Dividing this number by the total number of purchases (3) gives us our final result – $56.96.

Aggregated View:

Aggregated View 

Detailed View:

Detailed View 

$11.76 + $112.08 + $47.03 = $170.87

/3 = $56.96

I’ve attached a workbook to provide clarity for each of these scenarios. Tableau’s Online Help also provides some great information regarding Grand Totals

Personally, I know that seeing the numbers written out in an equation can sometimes help to break down the confusion of a crosstab. Hopefully, walking through this simple example gives you a better understanding of Tableau’s extensive functionality and an easier approach when working with your own data.   

More About the Author

Katie Wagner

Analytics Consultant | Training Lead
Preppin’ Data Project: Week 1 Note: A big thank you to Carl Allchin and Jonathan Allenby for initiating the Preppin’ Data project for our community. Hunker down, ...
Split and Pivot Your Data with Tableau Prep With the release of Tableau Prep in April 2018, analysts have been given an incredibly valuable tool for their analytical kit. ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072