3 Replies Latest reply on Jan 23, 2018 1:44 PM by Manuel Velasquez

# Aggregating two different averages per each month

Hello community

I have the following challenge:

I'm trying hard to vizualise two bars that represent the average capacity on the top 5 days and another with the remaining days (without weekends).

Data Source

Each row is for a date and the columns consist of productive data and staff availability.

[Total all regions] is the total workload and [Total Staff] is the available staff. [Capacity] is simply the productivity divided through available staff which reults in the teams capacity for that particular day.

In Sheet 1 the scope is to compare capacity with the total of all regions. At the moment in this draft there is only the general capacity for the whole month.

Now this is where I want to get:

I want twin bars one showing the average capacity of the top 5 days as far as [Total all regions] is concerned. And another bar that shows the average capacity for the remaining days of the month (exluding the weekends which always will have a null value).

Any suggestions how to solve this in a pragmatic way?

I tried through ranking (RANK) the date by volumes each month, but didn't get far.

Example attached.

• ###### 1. Re: Aggregating two different averages per each month

Hi Manuel

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)

AVG Capacity

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.

Manuel Velasquez

1 of 1 people found this helpful
• ###### 2. Re: Aggregating two different averages per each month

Hello Manuel

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.

• ###### 3. Re: Aggregating two different averages per each month

Hi Manuel

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

Capacity= [TotalAllRegions]/[TotalStaff]

AverageCapacity = SUM([Capacity])/COUNT([Capacity])

The view

Attached .twbx for reference

Let me know if this helps!

Manuel Velasquez