Average is not getting calculated when LOD granularity matched with View but when It doesn't match It calculated correctly.
Your LOD is basically finding the total sales for each category and sub-category. Essentially, the values below:
You are then averaging the value. Because the AVG is not part of the LOD, it will take the viz level-of-detail into account. So, if sub-category is not in the view, as shown below:
Tableau will add up the values for each sub category, then average them. For example, using Furniture, it will add up all the values to get 742,000 and will divide by 4 (because of the four sub-categories) to get 185,500.
If you add the sub-category to your view, that changes the viz level-of-detail, which will then change how the average works. Basically, it'll just divide each line's total by 1 (because there is only one unique category/subcategory), so the average will be the same as the total sales.
So, the question is what do you want this to show? Can you explain what it is you're trying to do here? What are you trying to average? With that information, I think we can find the best solution.
