3 Replies Latest reply on Dec 4, 2018 11:56 AM by Simon Runc

    Average Not Displaying Correctly in Tableau 9.3

    Larsen Rennie

      Hello All

       

      i have created two sheets in tableu 9.3. i have used Category and subcategory into the row shelf and  dragged sales into the text  shelf. I am calculating average of the Sales. Now the problem is that when i trying to calculate the average of all the subcategory within a Category in a separate excel file i am getting different result than what Tableau is displaying me . For example  under furniture category i am getting subcategories as

      Bookcases, chair,Furnishing, and tables and avg value for them is  357, 501,108,517 . So if do the average of these numbers in excel it will come up around 370.75  for Furniture which is correct but in Tableau when i remove subcategory from the row shelf it is giving result as 296.6 for Furniture. I think same is the case with others subcategories also. Can some one please tell me why is tableau showing incorrect result and what is happening underlying tableau. I searched through forum but i dont want to use the subtotal solutions and then set the compute to  calculate using average . I dont want to display substotal on the sheet and we want to present a high level view without showing subtotal and with correct average.

       

      Can anybody please suggest a solution to my issue and explain me what is happening underlying '

       

      Thanks to all in advance

       

      Attaching the twbx file for your refrence

       

      Jonathan Drummey

      Deepak Rai

      Andy Kriebel

      Simon Runc

        • 1. Re: Average Not Displaying Correctly in Tableau 9.3
          Simon Runc

          hi Larsen,

           

          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

          • 2. Re: Average Not Displaying Correctly in Tableau 9.3
            Larsen Rennie

            Hi Simon

             

            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

             

            Thank you

            Larsen

            • 3. Re: Average Not Displaying Correctly in Tableau 9.3
              Simon Runc

              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!!