4 Replies Latest reply on Nov 20, 2018 9:06 AM by Jeremy zhang

    Calculate on the result of in table calculation

    Jeremy zhang

      Hi Gurus

       

      There are four columns in this table and column 3 and 4 are in table calumniation. I need to have a column 5 ,to calculate the % difference of column 3 and 4. Please help!    My tableau version is 8.2

       

      Thank you very much in advance!

       

      Jeremy

       

       

        • 1. Re: Calculate on the result of in table calculation
          Simon Runc

          Wow Tableau 8.2! I'm afraid I don't still have this version installed to post and example, but I can talk you through it.

           

          When you use the "quick table calculation" to do share of total, Tableau (in the background) is creating a formula which looks like this

           

          SUM([Sales 2017]) / TOTAL(SUM([Sales 2017]))  for Sales 2017

           

          and

           

          SUM([Sales 2018]) / TOTAL(SUM([Sales 2018]))  for Sales 2018

           

          If you create these as 2 formulas (calculated fields) you can then create a further calculation taking one away from the other to get the variance.

           

          Hope that makes sense, and does the trick

          • 2. Re: Calculate on the result of in table calculation
            Jeremy zhang

            Thank you very much Simon!!

             

            I created the 2 formulas, but it does not work as expected.

             

            So I created 4 calculations to see where is the issue.

            Calculation 1= SUM([$ Sales 2018])

            Calculation 2= total(SUM([$ Sales 2018]))

            Calculation 3= [Calculation1]/[Calculation2]

            Calculation 4= SUM([$ Sales 2018]) / TOTAL(SUM([$ Sales 2018]))  I do not want calculation 4 do the in table calculation, but could not find a way

            Which part I did incorrectly? Calculation 3 and 4 should not show 0

             

            Please help!

            I hope I can attached my tableau file, but it is too big to be attached here.

             

            Thank you again!

             

            Jeremy

             

            • 3. Re: Calculate on the result of in table calculation
              Simon Runc

              So I think your calculations are (in the main) correct, it's just that you haven't set the display to percentage. As you have them as regular numbers to zero decimal places Tableau is rounding them to zero (eg the first product is 42.88% = 0.4488 which rounds to zero). If you change them to percent it should show the right results.

               

              I say in the main, as generally when you are comparing 2 percentages you want the difference, so want to take one away from the other and not divide them. I don't know your exact use-case, and there are times you would want to do this (although again, generally, you'd show the difference not the ratio), but again change the formatting of this field to a percentage and it should show.

              • 4. Re: Calculate on the result of in table calculation
                Jeremy zhang

                You are right!! I think I got what I need now.

                Thank you very much!!