6 Replies Latest reply on Aug 6, 2018 11:54 PM by Zhouyi Zhang

    How to calculate the average of an existing table calculation?

    philipp.gntzel

      Hello altogether!

       

       

      currently I am struggling with a problem concerning table calculations.

       

       

      What I want to achieve: Show a bar Chart, where each bar represents the percentage/relative difference to the previous year. This is quite easy since it can be easily done with the table calc functions. BUT know I want a bar on the far Right of the diagram, which visualises the average of exact those percentages. E.g. I have bars for the months Jan, Feb Mar, which values of 0,3%; 0,5%; 1%...thus the far Right average bar should Display a bar with 0,6%.

       

      I do not know how to achieve this. Obviously you have to create the average bar in another sheet, but since the table calc is in another sheet this is IMO not possible.

       

      I am glad for any suggestions :-)

       

      Phil

        • 1. Re: How to calculate the average of an existing table calculation?
          Zhouyi Zhang

          Hi, philipp

           

           

          It will depend on how your dimensions and how you build your view. Can you provide a sample workbook so that it will easier to help?

           

          ZZ

          • 2. Re: How to calculate the average of an existing table calculation?
            philipp.gntzel

            Hi Zhouyi,

             

            thank you for your answer!

             

            Unfortunately I cannot provide a sample workbook, because the data is confidential .

             

            But I will give some more Details of what I have done so far:

             

             

            I was given regular Sales data, where I have put years in columns and months in rows (classical crosstab). The measures I have put in there are the Sales data of the years 2017 and 2018.

             

            So far so good. Now I have calculated the difference percentage for each month compared to the previous year, thus the column 2018 now Shows the relative Change compared to 2017.

             

            So everything seems fine now. I have plotted it as a bar Chart. What I want to do now is to create one bar, which represents the average over These months. Since it is a table calc I cannot Change the view without changing the table calc itself, thus I cannot create one bar.

             

            Best regards
            Philipp

            • 3. Re: How to calculate the average of an existing table calculation?
              philipp.gntzel

              To be a bit more specific:

               


              What I have created in Tableau is the following table:

               

                             2017          2018

              Jan                            0,3%

              Feb                            1,5%

              Mar                            1,3%

              Apr                             1,2%

              May                            0,5%

              Jun                             1,7%
              Jul                              3,0%

              Aug                            2,45%

              Sep                            1,3%

              Oct                             0,45%

              Nov                            1,7%

              Dec                            0,2%

               

              In this table I created the percentage difference to the previous year 2017, thus not showing any values.
              I want to calculate the average of all those percentage numbers. Of Course when I drag out the month-dimension Tableau will Change the table calc and give me a value of the whole difference percentage 2017/2018.

               

              I hope this helps a Little bit :-)

              • 4. Re: How to calculate the average of an existing table calculation?
                Zhouyi Zhang

                Hi, Philipp

                 

                I use superstore data as example for your reference.

                 

                below are steps

                 

                1st, create the avg and set up table calculation

                 

                2nd, keep the first row by using first()=0 as filter

                 

                Final result

                 

                 

                Hope this helps

                 

                ZZ

                • 5. Re: How to calculate the average of an existing table calculation?
                  philipp.gntzel

                  Wow, thank you Zhouyi! :-)

                   

                  Unbelievable, that you responded so fast! Could you explain to me why this works? Especially the part with First()=0 seems to be the key to the solution, isn't it?

                   

                  Thank you again!

                   

                  Philipp

                  • 6. Re: How to calculate the average of an existing table calculation?
                    Zhouyi Zhang

                    No worries. I am glad to help, but unfortunately I have to use the sample data to show the steps

                     

                    The purpose to add First() = 0 is we can't filter the month from the data, so there will be 12 rows in the view, and for display purpose, we just need keep one.

                     

                    The rest is just about the settings of table calculations, you can follow my steps and see if you stuck somewhere.

                     

                    ZZ