So what's happening here is that Tableau is creating the average from the Row Level data (not the aggregate data). It's a good question, and get's to the heart of what many people struggle with when moving from Excel to Tableau.
The grain of the Superstore is OrderID/Product, which means that each row is a OrderID/Product combination. When you take the SUM, say, Tableau adds us all the OrderID combinations (rows) at the level you've got in the vizLoD (in the case of your table that is Category/SubCategory/Year/Quarter, and similarly when you use Average it's the average of each of these rows.
If I click in one of the cells in your table, and look at the underlying data, I can see that the Furniture/Bookcases for Q1 2013 is made up of 10 rows
and Tableau is taking the average of these 10 numbers
I like to think about things as having 3 levels...
Data Level of Detail: The level of the data, in this case OrderID/Product
Calculation Level of Detail: The level at which you want a calculation performed
Viz Level of Detail: The level that you want to display your data at
If you get these 3 concepts ...you'll be 90% of the way there!
Hope that makes sense
That's a very nice explanation by you and now i understood why tableau was showing different average values when i aggregated it to Category level. I however resolved this issue by using Lod calculation. if you have a better way of doing it or even with the LOD can you post a workbook with the solution. That will be quite helpful for me in the future
Glad it helped and made sense.
And yes an LoD (either Fixed, or the Include/Exclude variety) is the way to go. This is what they were invented for, when the required calculation LoD (level of detail), the data LoD, and the required display LoD are different.
Pre-LoD we did these with Table Calculation, where we'd need to have the required calculation LoD we needed, in the Viz and then get a bit funky with the Table Calculations so they would then display the correct Display LoD. ...LoDs make this so much easier
...it's all about the LoD!!