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
Resource Need Dummy Data V2.xlsx 22.5 KB