3 Replies Latest reply on Mar 27, 2013 11:26 AM by Alvarez Soler

    Customize Grand Totals

    Alvarez Soler

      Hi,

       

      I read all post and tried everything to display the grand total as the sum of all rows (averages), but nothing worked. I'm working with Tableau v8 (beta), can it be the problem?

       

      I tried 2 formulas:

       

      • If MIN([school_code])!= MAX([school_code]) then

      WINDOW_SUM(avg([paying_int_students]))

      else

      avg([paying_int_students])

      end

       

      (and nothing changed -see first_formula.jpg attached- )

       

      • IF LAST()== 0 THEN

      WINDOW_SUM(avg([paying_int_students]),IIF(LAST()=0,FIRST(),0),0)

      else avg([paying_int_students])

      END

       

      For that formula, I duplicated the category ("school code") at the level of detail and compute using "school code", but the sum of avgs appears in the last row (a single school that should show 10 students) and not the Grand Total!!

       

      (see second_formula.jpg attached)

        • 1. Re: Customize Grand Totals
          Joshua Milligan

          Alvarez,

           

          I've attached a packaged workbook to demonstrate a possible solution. I'm using the steps detailed for the Table Calculation option for customizing grand totals as described here:  http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/

           

          Note that the solution involves duplicating the School Code field.  This allows you to address that field in the table calc, which is essential because when in the Grand Total the original School Code field will be "All" (all values), so there is no way to get the averages for each code (the window size is 1).  But using the value when First() = 0 allows you to only perform the calculation once in the grand total.  I've not included the additional windowing optimization as the Tableau 8 data engine will not have the same issues present in 7.

           

          Also, note that Tableau 8 does stack all marks by default, so you might notice some difference if you turn it off and on from the Analysis --> Stack Marks menu options.

           

          Regards,

          Joshua

          • 2. Re: Customize Grand Totals
            Alvarez Soler

            Thank you very much Joshua. I was assuming that simply dragging and dropping the dimension field in the level of detail shelf I was creating a copy, bu I had to duplicate the dimension first and then include the copy.

             

            Thanks for the help!

            • 3. Re: Customize Grand Totals
              Alvarez Soler

              Joshua,

               

              And if I want to show just the Grand Total? I removed the "school_code" from the rows shelf, but now it is showing the Grand Total as if it was the first school_code (273,000 students for ABU univeristy) and not the total of all schools:

               

              Show Gransd total.png

               

               

              Regards