6 Replies Latest reply on May 29, 2013 8:03 AM by Jonathan Drummey

    Total of Max

    nattaporn Vichittongrueng

      I have a set of data as in the attached file.

      "Budget manday" is the budget for the job, ie. Budget for Job no. 1001 is 10 Manday (not 40).

      I'm trying to add grand total for each column and I can not have the correct value for "Budget Manday"

        • 1. Re: Total of Max
          Jonathan Drummey

          Tableau's Grand Totals are computed as a separate calculation at a coarser level of aggregation, so where you have MAX(Budget Manday) as the measure, in the Grand Total row Tableau is computing the MAX(Budget Manday) across the data set and that's why it's coming out as 15.


          See the series of three blog posts starting with http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-1, they go into a variety of workarounds.

          1 of 1 people found this helpful
          • 2. Re: Total of Max
            Shawn Wallwork

            Jonathan, you got this whole text table rap down -- I'm truly impressed!



            • 3. Re: Total of Max
              Jonathan Drummey

              Practice. Way too much practice.

              • 4. Re: Total of Max
                Shawn Wallwork

                And if only all here knew what you truly mean by 'practice'.




                PS: Enjoyed this week greatly, will be fun to Digest.

                • 5. Re: Total of Max
                  Joe Mako

                  Another options is to use data blending, a separate data source of each data level.


                  Notice that I used custom SQL with select distinct, to generate the other data sources, these tables would likely exist in a normalized database.


                  For this exact situation, I think this may be better than complex calculations.

                  1 of 1 people found this helpful
                  • 6. Re: Total of Max
                    Jonathan Drummey

                    Taking a break from writing documentation this morning to go through some unread emails and found this thread. That is yet another brilliant solution, Joe!


                    Here's the why, for my own notes as well as others:


                    The situation is that the raw data is at the level of Row ID and there are measures at two higher levels (Budget Manday at Job No and Budget at Employee) that are included in the data, so the Budget Manday and Budget values are repeated. Since the goal is to have the subtotal row show the right aggregation (i.e. no aggregation for Budget, summed up for Budget Manday), things get a little complicated.


                    Subtotals and grand totals are the same computation used in the detail area, just separately performed at a higher/coarser level of aggregation. In the case the subtotal on Employee on Rows, that means that the subtotal computation is effectively removing Job No from the view. The secondary data sources are set up so they will return the correct value when SUM'ed in the detail view, and then return the correct value in the subtotal computation as well.