1 Reply Latest reply on Oct 29, 2018 6:06 AM by Andrew Goldman

    Sorting on Grand Total/Sum of Max Values - Seems Impossible?

    Andrew Goldman

      This issue's a bit of a doozy to explain, but hope it'll make sense -- I've gone ahead and attached an Excel workbook with the data and two basic pivot tables to illustrate the same issue I'm seeing in Tableau that I can mostly replicate in Excel (with the same issue) - hoping someone has a workaround or some advice!

       

      I have a situation where I'm trying to map staffing needs to potentially available resources.  So based on my attachment I can say the following:

       

      In November, I have 3 staffing needs:

       

      - Resource Need 001 - Needed for 160 hours (full-month)

      - Resource Need 002 - Needed for 80 hours (half-month)

      - Resource Need 003 - Needed for 80 hours (half-month)

       

      In December, I have 2 staffing needs that are repeats of the above since they're continuous needs as we enter the holiday season:

       

      - Resource Need 001 again - Needed for 120 hours (3/4-month)

      - Resource Need 002 again - Needed for 160 hours (full-month)

       

      In order to find out who's available, I've gone ahead and "inner joined" my available resource table for those two months which consists of John and Jane Doe, inner joining on month available/month resource needed:

       

      In November, John Doe's available for 80 hours and Jane Doe's available for 120 hours, so either of them could help with any 3 of the needs completely or to some extent (if resource need 1)

      In December, John Doe's available for 80 hours and Jane Doe's available for 160 hours, so likely Jane is the best fit for one of them, but maybe John could help out too as a half-resource on Resource Need 5

       

      In order to illustrate this in Tableau, I created two tables much like the pivot tables in the Excel attachment - one which shows the max(resource need requirements) of the resource need by month and one which shows the max(hours available) by resource available by month

       

      In Tableau in Analysis -> Totals, you can actually set Tableau to create a Grand Total and sum up the Max'd numbers together so instead of Excel showing 160 hour needed for Resource Need 1, it would actually show 160 + 120 = 280 = total hours needed for Resource Need 1 over the course of November and December which is GREAT, but by making that change, you lose the ability to sort it altogether, and so I might end up with a wonky order of 280 (row 1), 80 (row 2), 240 (row 3) vs. 280 -> 240 -> 80 using the first pivot table in the attached as an example

       

      Does anyone have any idea of how to resolve this sort of issue so I could use Analysis -> Totals -> Automatic which re-enables the ability to sort?

       

      One immediate thought was to change the data for Resource Needs Requirement to be SUM([Resource Need Requirements for Resource Need 1]) / COUNT([How Many Instances of Resource Need 1 in October]) t to make the totals add up correctly and not have to use MAX() at all which does indeed work, but then lets say I filter on "John Doe" as a potential resource letting the second pivot table/worksheet act as a filter for the pivot table/worksheet above it to consider to fit the need, it would appear as if Resource Need 1 only needed 80 hours as opposed to in reality needing 160 since filtering on John Doe forces the data to only look at row 1 of the raw data (again per the attached).  To get out of that, I'd switch back to the MAX() option and do a grand total sum of the maxes, but then I lose the ability to sort again

       

      Many thanks in advance, and apologies if this was confusing!  Really at the end of the day I'm just looking to be able to sort on the grand total of the sum of max values and my problem's resolved!

       

      QUICK EDIT: Fixed attachment and title

        • 1. Re: Sorting on Grand Total/Sum of Max Values - Seems Impossible?
          Andrew Goldman

          For those who are struggling with the same or a similar issue, I've found a solution!

           

          The best thing to do in this situation for me was to turn off the grand totals altogether and create a custom calculation that re-calculated the row grand total formulaically, use this new table custom calc measure as a dimension, and then sort on the dimension in descending order to mimic the same functionality as standard grand total sorting.  Originally I thought I could just do a window_sum on the row and sort that way, but after I learned you couldn't sort on table calcs used as dimensions (as much as I tried), I learned I had to re-write the window_sum custom calc to be an LOD expression as follows:

           

          {EXCLUDE [Month]:

              SUM({include [Resource Need ID],[Month]:

                      MAX([Resource Need Requirements (Hrs)])

                   })

          }

           

          Once this formula worked out all right, I dragged the new custom calc field to the first pill space in the row shelf, changed it to be a discrete measure instead of a continuous measure, right clicked the header of this new "Grand Total" field on my report,  selected "Sort Descending" and it worked like a charm!

           

          Advice courteous of Tableau's how to help here: https://kb.tableau.com/articles/howto/sorting-by-fields-with-table-calculations