Tableau Basics: SUM vs AVG

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:

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

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:

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.

Now we get the correct answer to our question.

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.