3 Replies Latest reply on Jan 30, 2017 4:32 AM by Priscila Marin

    How to Sum Column for Values Computed by Formula

    Wayne Ooi

      Hi,

       

      I am trying to sum the values of a column (that was computed by formula) but Tableau applies the row level formula to it instead.

      I am wondering if anyone can show me what is the effective and correct way please?

       

      SumColumn.png

       

      Thanks,

      Wayne.

        • 1. Re: How to Sum Column for Values Computed by Formula
          Joshua Milligan

          Wayne,

           

          The difficulty here is using table calculations which work great, except in the Grand Total line where the level of detail is different than the rest of the view (which is at a Segment level of detail, but the Grand Total doesn't have it).

          Sometimes, you can create a copy of the dimension that defines the level of detail, place that on Detail and force the Grand Total to that level (then roll things up using a Window_Sum() function).  But that's going to get quite complex given all your grand totals.

           

          So, in this case, you might want to consider getting rid of all table calculations (e.g. TOTAL) and instead re-write a few calculations as LoD calcs (there are only 3!).

           

          [Mix Vol - FC] changes from:

          SUM([Vol-FC]) / TOTAL(SUM([Vol-FC])
          

          to

          SUM([Vol-FC]) / SUM({EXCLUDE [Segment] :(SUM([Vol-FC]))})
          

           

          Notice that I changed the TOTAL to an EXCLUDE LoD.  Given that Segment is the only dimension in the view, excluding it gives the same result as a TOTAL.  This becomes important in a bit, where we'll fix the Grand Total and won't be able to use table calcs...

          (BTW, if your final view is more complex, you'll have to determine if there are other dimensions that need to be excluded)

           

          [Mix Vol - TY] changes from:

          SUM([Vol-TY]) / TOTAL(SUM([Vol-TY])
          

          to

          SUM([Vol-TY]) / SUM({EXCLUDE [Segment] : (SUM([Vol-TY]))})
          

           

           

          And finally...

          [Mix Effect] changes from:

          ([Rate-TY] - TOTAL([Rate-TY])) * [Mix Var]
          

          to

          {INCLUDE [Segment] : ([Rate-TY] - SUM({EXCLUDE [Segment] : ([Rate-TY])})) * [Mix Var]}
          

           

          Here's what it's doing:

          • The basic formula is still there  ([Rate TY] - Total Rate TY) * Mix Var

           

                    {INCLUDE [Segment] : ([Rate-TY] - SUM({EXCLUDE [Segment] : ([Rate-TY])})) * [Mix Var]}

           

          • What changed is that the total is calculated by using an Exclude (as explained above) but the entire calculation is done as an INCLUDE, bringing Segment back into the level of detail.  Everything is already at a Segment level (so it works as it did before), but the Grand Total which was not previously at the Segment level (and thus was wrong) is now calculated at a Segment level and is right!

           

          Hope that helps!

          Joshua

          2 of 2 people found this helpful
          • 2. Re: How to Sum Column for Values Computed by Formula
            Wayne Ooi

            Hi Joshua,

             

            Thank you very much for your help. I have updated my report accordingly to your suggestion and the report works fine now.

            Thank you also for explaining why using Include and Exclude of LoD function in the calculation. It's good to know the differences.

             

            Thanks,

            Wayne.

            • 3. Re: How to Sum Column for Values Computed by Formula
              Priscila Marin

              Hi Joshua

               

              Very usefull your explanation. But I have a doubt, in my case I have this formula:

               

               

              %Dif = (ZN([*teste_formula]) - LOOKUP(ZN([AVG-ITEM]), -1)) / ABS(LOOKUP(ZN([AVG_ITEM]), -1))

               

              AVG_ITEM = sum({ INCLUDE  [ITEM] : sum([VLR TOTAL LIQ])} ) / sum({ INCLUDE  [ITEM] : sum([QTDE])} )

               

              Var %Dif * Fat2016 =  [*% Dif] * sum({ EXCLUDE :sum([Fat_2016])})

              Teste_Pricing.jpg

               

               

               

              I need that column "Var %Dif * Fat 2016" results -214

               

              Can you help me?

               

              Thank you

              Priscila Marin