I have a Tableau dashboard connected to an Excel file. When I check some numbers in the dashboard against the original Excel file, I am finding some strange differences. Can someone explain this to me? Here's an example.
This is an image of the Excel spreadsheet showing responses to several survey questions (in wide format--I pivoted it in Tableau). The last column averages across columns and then there is an average of the averages highlighted in yellow.
Here is the same data shown in a few views in Tableau
As you can see, I'm getting the same responses by survey question as in the Excel data above.
I'm also getting the same averages across survey quesitons.
However, the average of the averages is different! What's going on?
Thanks in advance for helping me to understand this!!!!
When you break it out by survey, you get the average for that survey.
When you remove that dimension, you get the average of all rows, not of all surveys.
Sometimes these are equal. But as an extreme example, suppose the first survey had 1000 rows, and all the other surveys had 1 row each. Suppose the first survey had an average of 10, and 9 other surveys had an average of 9 each. You can see that the average you get from survey#1 will overwhelm the remaining single-row surveys. 10 + (9x9) = 91. Divide that by 10 and you get 9.1. But at the individual row level you'd have 10081 divided by 1009, which is 9.99.
If you want to overall average to come out to 9.1 instead of 9.99 you have to sum up the individual survey averages and divide by the number of surveys.
If you can't get that to work, upload a sample workbook and I'll show you with your example.
