First time users of Tableau often get tripped up over the default Tableau SUM aggregation.  Here is what I mean.

Suppose the question is to find the average of SALES PER VISIT (sales measured across the preceding 6 months) among the males and females in a sample of 25 shoppers.  The data look like this in Excel:

Tableau and Excel


TIP:  We can easily input these data to Tableau by cutting and pasting the selection into the Tableau canvas:


Cut and paste data into Tableau

Now to answer the question.

First time users of Tableau may correctly put GENDER on the row shelf and SALES PER VISIT on the column shelf.  Tableau defaults to a bar chart yielding:

Tableau bar chart


First time users may also put SALES PER VISIT on the Label Marks card, which displays the value next to each bar in the chart.  They may even put GENDER on the Color Marks card to give the viz some pop.

And then they call it done.  Males spend more on average than females.

But do they?

We note that the green pills show SUM(Sales per Visit).  Tableau’s default “aggregation” is to sum the values across the rows in the data set.

Going back to Excel, if we sum SALES PER VISIT by GENDER across the 25 rows, we get, using the SUMIF function:



This is exactly what Tableau shows.

But we want to find the average.  In Excel, using the AVERAGEIF function, we see that females spend on average $11.62 while males spend $9.13 per visit.

To get Tableau to match, we simply change the aggregation by right-clicking on each of the green pills and select Measure (Average) from the drop-down menu.

Tableau change aggregation


Now we get the correct answer to our question.


Tableau correct aggregation


If Tableau is not yielding the correct answer, try thinking about how you would do it in Excel.  Sometimes, but not always, this will provide the proper guidance.

Topics: analytics, research, Data Collection

Kevin Duffy-Deno

Written by Kevin Duffy-Deno

Dr. Kevin Duffy-Deno, with over 30 years of experience in quantitative research and analysis, is an expert in econometric, data, statistical and predictive modeling and data visualization. Both with Bintel and prior organizations, Dr. Duffy-Deno has been engaged in numerous projects requiring data acquisition from proprietary and public sources, the normalization of such data and the integration of data into a format supporting analytic analyses and products.