6 Replies Latest reply on Dec 16, 2015 1:32 AM by Dannie Korsgaard

    Beginner question: Wrong averages when working with joined normalized excel sheets

    Dannie Korsgaard

      Dear Tableau ninjas,

       

      I apologize if this is a trivial question, but I have not been able to get my head around this issue even though I found what appears to be the solution here: Removing Duplicated Data after Joining Tables | Tableau Software . Unfortunately the example given is so simple that I had a hard time translating it to my scenario. So here is my problem - I will appreciate any advice - Im sure this will be easy to most of you.

       

      I am analyzing data from food diaries. Before importing the data into Tableau I have tried to bring the data into (what I think you call) "normalized" mode (see attached tableau_example.xlsx). I have a "master table" ("person data"), which contains some basic info and ratings given by the owners of diaries. In the example attached to this question I have only included one rating called "SATISFACTION_WITH_FOOD_EXPERIENCE". If I import the table into Tableau (tableau_example.twb) and create a visualization (Sheet 1) showing the average of this rating it will correctly display 7.7.

       

      Now the excel file also include food items which the owners of the diaries have listed. I have therefore created a table ("food items") for listing food items with a unique key ("FOOD_ID"). As we have a many-to-many relation between owners of the diaries and the food items they can list, I have created a table for matching owners with food items ("per_food"). If I now in Tableau drag first the per_food table in and do a left join with the already imported person_data and after that left joins food items with per_food, I will get a weird average (8.337) if I return to Sheet 1. If I right click on the visualization and select "view data" and then the "underlying" tab I can see that it is because of the join and duplicated values that I get this wrong average. As I would like to do stuff like the visualization in Sheet 2 it annoys me greatly that I cannot figure out how to make Tableau do the super simple average on SATISFACTION_WITH_FOOD_EXPERIENCE. Is there a way to do joins per sheet or is there like a best practice here? It sounds like I have to do something similar to http://kb.tableau.com/articles/knowledgebase/removing-duplicated-data-after-join  but as I said - I have not been able to make this work in my case. Please help this lost beginner :-) ! and have a nice christmas!