Let me show you one approach that you could use, this approach requires some data preparation ( Pivoting the data), Creating Sets, and some calculations, I created a copy of the file you provided and clean up some calculations, except for the total staff calc.
Pivoting the data.
To get the productivity throughout all regions I will pivot those 4 columns
I'll rename the resulting columns as Pivot Field Names="Regions" and Pivot Field Values="Productivity"
Creating the calculations
Capacity ( As you defined)
NOTE: on Average calculations "NULL" are ignored
Creating the Sets:
So for your request of showing the avg capacity for the Top 5 days by productivity and the rest of the month, I will create a set based on Datum dimension to pick those top 5 days
Double checking the Set result ( In - Out).
Elements IN means = IN the top 5 per productivity
Elements OUT means = OUT of the top 5 per productivity
You can change the aliases to make it user-friendly
Showing the AVG Capacity calculation for the Top 5 days and rest of the month
Let me know if this help!
Attached .twbx file for reference.
Workload vs Capacity - Copy.twbx 65.4 KB
Thank you for your input, very much appreciated.
The following problems appear:
- Pivoting the data muliplies the availalbe staff by region resulting in a lower capacity by factor 4. This would need minor adjustments in some formulas to solve it. I would pivot the data only if this is unavoidable and adds added value. Reason: I have other work sheets on this data which work already well. A pivot would create issues with them. The capacity does not have any relation to the workload in the regions as staff works always simultaneously in all regions. Therefore please ignore the regions, they don't have any impact on the of the capacity calculation.
- The Set does not consider each month separately. Apologies if I didn't point this out more clearly in my initial post.
The final result should be something like this:
One bar representing capacity on the top 5 days and the other bar representing the other days.
I added the worksheet and in Sheet1 there is my draft. The side by side bars look already fine, but the Top5 days are on a yearly not on a mothly level. If this can be solved then I am fine.
Having the line graph of [Total all regions] in the same sheet would be nice to have. Otherwise I could combine the two in a dashboard.
Workload vs Capacity.twbx 38.4 KB
Ok, Ranking per month, in Tableau when using Tableau Calculations (Rank in this case) you need that level of detail on the view (in this case every day), and as I understand you want to present the data at a monthly level.
So, if we are not able to re-shape the data, the other option could be doing the RANKING PER MONTH at the data source level, and keeping the same structure.
In this other approach, I'm using SQL Server and a Custom Query to create the ranking per month.
This is the custom query to create the ranking per month ( I also create TotalAllRegions and TotalStaff at this level also, but you can remove from the query if you already have it on Tableau)
Result, notice that the ranking reset every month
Creating the Set
Now that we have this ranking at the row level, create the set for the top 5 per month.
NOTE: We need to convert "RankInMonth" field to a dimension before doing the set as Tableau see it as a measure.
NOTE: Capacity and AverageCapacity calculations are the same as you defined
AverageCapacity = SUM([Capacity])/COUNT([Capacity])
Attached .twbx for reference
Let me know if this helps!