First, welcome to Tableau, and the Tableau Forums!
For future reference, you will be more likely to get good responses in the forums if you attach a sample workbook which illustrates your problem. I would encourage you to read Getting the Most From the Tableau Forums and view Anonymize your Tableau Package Data for Sharing for more information.
I used the information you provided to create my own workbook which is attached. In it, you will see that the average for product 2 is 67, not 67.5 as you state.
What you want can be done using Level of Detail (LOD) expressions, which you will see in the calculated field [Avg Product Score], and then using an average of that field, as shown in the sheet "avg of avgs." However, I do question this desired outcome, since taking an average of averages is generally not considered mathematically valid.
I hope that helps.
Avg of product score avgs.twbx 12.1 KB
Thanks much Bill.
This is the solution I was looking for.
I am trying to solve it in v8.3, where LOD is still not available. How can I achieve the same in version 8.3?
Is there a reason you do not want to upgrade? It would be considerably more difficult to do in 8.3. I choose not to go backward in my skills.
“Without data, you are just another person with an opinion.”
It is more of organisation decision. Appreciate you helping in this Bill.
It would be awesome if you can direct me to pointers or documentation to achieve this in 8.3 which I can take a look
1 of 1 people found this helpful
In 8.3, you can do this using table calculations. Since there is no workbook, I will take a guess.
First, you need to have Product in your sheet for this but that doesn't mean you need to show it.
Assuming you have Product--Department--City---City_Tier- all in the viz.
Let's say whatever you don't want to see in the sheet is in detail shelf e.g. Product.
Write a calculation like WINDOW_AVG(sum(score)) assuming what I see in the example table is sum of score for a given row for those dimensions. Let's call this Calc1.
Drag this new field into your sheet. Right click on it and select edit table calculation. From compute using settings, select advanced and move product to partitioning window and everything else to addressing. This means Product is your window so you will get the average value within that window. Once you dismiss the dialog for each product you should see the same value repeating.
Create another calculation that contains INDEX()
Drag this into filter shelf. Edit table calculation and match the same settings as the previous one. Then select only 1 to keep in the filter pop up. What this does is to keep only one of those repeating values so you only get 1 row for department, tier, city combo.
Doing the second level aggregation will add more complexity since it adds another layer of table calculation and require a lot more setting adjustments to table calcs so it would be better if you shared a sample workbook. But basically you need to do the average by yourself since you don't want to get a weighted average. IT would be something like
WINDOW_SUM(avg you computed in previous step / number of times you see repetition)/total number of products
Since this seems to be complicated in 8.3 I will go ahead with a separate data source connection.
In parallel I will try this out some time and keep you guys updated where I reach.
Thanks, appreciate all inputs