8 Replies Latest reply on Feb 25, 2013 11:42 AM by Pete Staley

    Grand Total Average Using Multiple Measure Names/Values

    Pete Staley

      Hello,

       

      I have a text table that calculates percentages across time. Each row represents a separate Calculated Field. The current workbook setup includes:

      • Columns: Date pill
      • Rows: Measure Names pill
      • Marks (Text): Specified Measure Values (the desired calculated fields)

      Objective: Calculate the grand total average for each measure value for the given time period.

       

      When enabling "Show Row Grand Total", the field shows up blank. My gut tells me that the blank grand total is resulting from Measure Names in the Rows shelf. (sample workbook attached)

       

      Does anyone have any ideas on how to achieve the desired outcome?


      Thanks!

        • 1. Re: Grand Total Average Using Multiple Measure Names/Values
          Shawn Wallwork

          Pete, Zen Master Jonathan has written up a three part series on Grand Totals. I'm certain you'll find your answer there.

           

          Part 1

          Part 2

          Part 3

           

          --Shawn

          • 2. Re: Grand Total Average Using Multiple Measure Names/Values
            Pete Staley

            Hi Shawn,

             

            Thanks for the information. I'm familiar with Parts 1 and 2, however these methods don't seem to apply to my particular issue. I was hoping to solve without Custom SQL. Perhaps that's the only option. Thoughts?

             

            - Pete

            • 4. Re: Grand Total Average Using Multiple Measure Names/Values
              Jonathan Drummey

              Hi Pete,

               

              Congratulations, you found an example of the use case I've been thinking about for a Part 4 of the custom grand total series! However, this situation is a) rare and b) really specialized to the particular data and requirements at hand, so it's hard to come up with a generalized solution.

               

              The basic issue you're having isn't Measure Names, it's that when you are in the Grand Total column Tableau effectively removes all dimensions from the Columns Shelf. So there is no Day(Date) available to the calc for addressing, there's only one partition with one value in the Grand Total column and the LOOKUP(sum(first_count),-1) returns Null and so the whole calc returns Null.

               

              A secondary issue seems to be that you are attempting to perform the same aggregation in the Grand Total column, which won't work because that needs to be an aggregation of the underlying aggregation (or the computation applied across the entire partition/data set, which is how most totals are computed in Tableau), otherwise you'd just be returning all the detail values to the Grand Total column. Since you'd revised the Total column label to "Average," I'm guessing you want an average there.

               

              What I did to solve this is a variation on Part 2 of the Grand Total series using both the test for Grand Total technique and aggregate calc for the Grand Total, I'll try to explain what's going on:

               

              1. Since the calc depends on a lookup, for the calc to work in Total column it needs to have Day(Date) in the overall level of detail when Tableau computes the grand totals. Since the Day(Date) on the Columns Shelf is "removed" from the grand total computation, we can do this by putting a copy of Day(Date) on the Level of Detail Shelf (hereafter called Date Copy).
              2. However, to get the calc to work in the detail columns, it needs a Compute Using of both the Date and Date Copy fields. Using just one dimension causes partitioning on the other field and that breaks the calc.
              3. However, this particular Compute Using breaks any test for the Grand Total column and the ability to compute over the Date Copy.

              The way to resolve this quandary is to use two separate calcs for each measure at different levels of aggregation, I set this up for the D1 measure in the attached.

               

              D1 Calc Feeder

              sum(d1)/lookup(sum(first_count),-1)

              This will return the values for the detail columns. Note that LOOKUP([some aggregate],0) is the same as [some aggregate] and will be slightly faster to evaluate.The Advanced... Compute Using is set to Date, Date Copy.

               

              D1 Calc (Window Avg)

              IF SUM([Number of Records])!= TOTAL(SUM([Number of Records])) THEN

                  IF FIRST()==0 THEN

                      WINDOW_AVG(sum([d1])/lookup(sum(first_count),-1),0,IIF(FIRST()==0,LAST(),0))

                  END

              ELSE

                  [D1 Calc Feeder]

              END

               

              This calc tests for the grand total column using a table calc instead of a regular aggregation as described in Part 2, then if it's the Grand Total column returns a single value for the window average. In Tableau v8 you can remove the ,0,IIF... optimization. This has a Compute Using of Date Copy, with the [D1 Calc Feeder] having a nested Compute Using as noted above.

               

              This calc is returning the average of the displayed values, which is not the true sum(numerator)/sum(denominator) of the data set. To do that, we use an alternate calc:

               

              D1 Calc (True Mean)

              IF SUM([Number of Records])!= TOTAL(SUM([Number of Records])) THEN

                  IF FIRST()==0 THEN

                      WINDOW_SUM(sum([d1]),0,IIF(FIRST()==0,LAST(),0))/WINDOW_SUM(lookup(sum(first_count),-1),0,IIF(FIRST()==0,LAST(),0))

                  END

              ELSE

                  [D1 Calc Feeder]

              END

               

              This has the same Compute Using settings as the D1 Calc (Window Avg).

               

              Note that I also removed the sort on Day(Date), the default sort in Tableau for dates is already ascending order.

               

              Hope this helps!

               

              Jonathan

              • 5. Re: Grand Total Average Using Multiple Measure Names/Values
                Pete Staley

                Jonathan,

                 

                Thanks for the outstanding response. Not only did you fix my issue, but you optimized my workbook and taught me some valuable Tableau knowledge. You sir, are a true gentleman.

                 

                - Pete

                • 6. Re: Grand Total Average Using Multiple Measure Names/Values
                  Pete Staley

                  Hi Jonathan,

                   

                  I tried recreating your solution on a separate tab in the workbook. While I can derive the grand total averages, the daily values aren't appearing.

                   

                  Steps:

                  • Created calculated fields (d1 calc feeder and d1 calc)
                  • Copied the suggested formulas
                  • Set d1 calc feeder's default table calculation to -> Compute Using -> Advanced -> Date, Date(Copy)
                  • Added Date(Copy) to level of detail
                  • On the Measure Values pane - Compute d1 calc using Date(Copy)

                   

                  Any ideas on what I may be missing here? Thanks!

                   

                  - Pete

                  • 7. Re: Grand Total Average Using Multiple Measure Names/Values
                    Jonathan Drummey

                    Hi Pete,

                     

                    Thanks for your detailed steps, they pinpoint exactly where the calcs diverged.

                     

                    The way to diagnose what is going on is to compare what Tableau is reporting for the addressing and partitioning settings for the different table calcs, either by hovering over the pill on Measure Values or going to the Edit Table Calculation dialog and looking at the Description section. This can be more difficult in cases like this where the same words (Day of Date, D1) get used over and over again, but it shows that there is a difference between what I'd set up and what you'd ended up with for the D1 Calc Feeder.

                     

                    Here's why: When you click on table calc's pill and set the Compute Using to a dimension, that sets the Compute Using for all nested table calculations.So that last step you took - set the Compute Using to Date (copy) - overrode the Compute Using of Date, Date (copy) for the nested D1 Calc Feeder that came from the default.

                     

                    Since the pills are in the view, the fix is to go to the D1 Calc measure, bring up the Edit Table Calculation dialog, Use the Calculated Field combo box to select the D1 Calc Feeder, and set the Compute Using for this instance of it in the view to Date, Date (copy). You'll need to do the same for both D1 Calc measures in the view.

                     

                    Here's the process to make sure this doesn't happen again as you are working with the other measures:

                     

                    • Created calculated fields (d1 calc feeder and d1 calc)
                    • Copied the suggested formulas
                    • Set D1 calc feeder's default table calculation to -> Compute Using -> Advanced -> Date, Date(Copy)
                    • Added Date(Copy) to level of detail
                    • Add the D1 calc to the view
                    • Click on the D1 calc's to Edit the Table Calculation. The Table Calculation dialog will default to having the D1 calc selected in the Calculated Field Combo Box.
                    • Set the Compute Using for this to Date (copy), then click OK. This sets the compute using for just the D1 calc and not the nested feeder calc.

                     

                    Hope this helps!

                     

                    Jonathan

                    1 of 1 people found this helpful
                    • 8. Re: Grand Total Average Using Multiple Measure Names/Values
                      Pete Staley

                      That helps tremendously. I get it, and everything works now. Thank you!

                       

                      - Pete