Tableau total average vs average per location

Question

Uploaded workbook on public tableau

I understand how to chart average per location in Tableau, i.e. create calculated field

AVG([Rating])

And then plot this vs the location.

But now, I am looking for average of ALL ratings, regardless of location, so I created calculated field

WINDOW_AVG([Avg_Rating], First(),Last())

If you see tab Grand Average, it equals 3.704, which I expect, calculated in EXCEL.

But if you see tab Avg per Location and hover over the bars, you will see

Tot_Avg = 3.848

How on Earth did this happen?

Andrew LaPrise Andrew LaPrise · Accepted Answer · 2015-09-08T15:02:41

Because with that WINDOW_AVG(), you are not excluding Location from the aggregation. WINDOW_AVG() is looking at all the rows in your partition. You've set up your rows by dragging Location and Avg_Rating into your view. Your partition looks like this:

| Location            | Avg_Rating |
+---------------------+------------+
| Belfrance Chocolate |  4.55      |
| Boston Tea Party    |  3.73      |
| Eat at Joe's        |  3.13      |
| ...                 |  ...       |  
+---------------------+------------+

So WINDOW_AVG() is calculating the average of all of those Avg_Ratings in your partition.

If you want to exclude Location from the aggregation, an intuitive way to do that is to use EXCLUDE. Try this:

{ EXCLUDE [Location (Loc)] : AVG([Rating]) }

This will exclude Location from the level of detail, so that your partition will include every row (for just this calculation), instead of an aggregation for each Location, and you'll average the original ratings instead of averaging the averages per Location.

Tableau total average vs average per location

1 Answers