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
          Manuel Velasquez

          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
              Manuel Velasquez

              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