7 Replies Latest reply on Aug 23, 2015 10:08 PM by Bora Beran

    Average at different grain in Tableau

    Sandesh Shridhar

      Hey Guys-

       

      I am a beginner in Tableau. I did search the forum but I don't think I got a same use case that I was trying to solve.

      My underneath table is like this

      Product--Department--City---City_Tier-- score

      product1- dept1- NY- tier1 -- 75

      product1- dept1- NJ- tier1 -- 82

      product1- dept1-XYZ-tier1-80

      product2- dept1- SEA- tier1 -- 70

      product2- dept1- MN- tier1-- 64

       

      I have two worksheets, one detailed view and the other is a summary view.

      In detailed,

      I am placing cities and city tiers in col shelf and product, department as rows. This all works fine. And my grand total average looks good.

      But in Summary view

      I just want to have score for  city tiers.  Though I don't have product  in my shelf  I want my score to be computed

      a) First average at product across cities( product 1: (75+82+80)/3=79, same for product 2=67.5 )

      b) And then average across products(total average to be (79+ 67.5)/2))

      But Tableau currently is averaging across all  5 rows and yields different number. Hence my total numbers in detail would not match with summary view.

       

      I tried going through table calculations, but challenge is since column I want to partition by(partition by in SQL world) does not exist in Shelf I found it very difficult.

       

      Any help would be very very much appreciated

        • 1. Re: Average at different grain in Tableau
          Bill Lyons

          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.

          1 of 1 people found this helpful
          • 2. Re: Average at different grain in Tableau
            Sandesh Shridhar

            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?

            • 3. Re: Average at different grain in Tableau
              Bill Lyons

              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.

               

              bl

              “Without data, you are just another person with an opinion.”

              • 4. Re: Average at different grain in Tableau
                Sandesh Shridhar

                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

                • 5. Re: Average at different grain in Tableau
                  Bora Beran

                  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

                  1 of 1 people found this helpful
                  • 6. Re: Average at different grain in Tableau
                    Sandesh Shridhar

                    Thanks Bora.

                    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