4 Replies Latest reply on Nov 14, 2016 10:02 AM by Brandon Petesch

    Summing Weighted Averages in Tableau

    Brandon Petesch

      I am currently working on a metric for my company and am having a little trouble trying to sum up the product of a weighted average LOD calculation in my workbook.  Essentially I am trying to calculate the weighted average by service class and order lines, but then I only want to show the distribution center in the final VIZ I am putting together.  In order to calculate the goal for that metric I need to first calculated a weighted average with the service classes showing.  I was able to achieve this by using the equation below.  With this output it appears that all I have left to do is sum up the product and remove service class; however, when I do so the summation does not appear to work properly.  

       

      Sum([Demand (Order Lines / Day)])/attr({ FIXED [Distribution Center]:sum([Demand (Order Lines / Day)])})*AVG([Target SL (%)])

       

      For example in my final output I should see 75.04% for the ADC and 95.68% for the EDC but it does not appear to be working properly.  I believe that I might need to use another, nested, LOD expression but cannot quite get there.

        • 1. Re: Summing Weighted Averages in Tableau
          John Croft

          I think this is what you want. I believe you were over thinking the weighted percent calc. No need to aggregate each of those pieces.

           

          • 2. Re: Summing Weighted Averages in Tableau
            Brandon Petesch

            This definitely helps John, but it looks like I forgot to mention that my target service level value is actually an average measure, see the screenshot from my actual data source below.  This means that I have to use an aggregate function, and when I do that with my original data it appears to change the outcome of the equation.

             

             

             

            Brandon Petesch

            Sr. Wholesale Supply Planner

            O: (414) 343-7418 C: (414) 218-9847

            • 3. Re: Summing Weighted Averages in Tableau
              John Croft

              So What are your new final output targets for ADC and EDC?

              • 4. Re: Summing Weighted Averages in Tableau
                Brandon Petesch

                Below is a snapshot of what the outputs now look like in my workbook that is linked to the source data.  In this example, you have multiple parts in each service class all with the same goal fill rate and that is why I needed to use the average of the goal fill rate as I did in my first equation.  When I remove the sum from the order lines and get rid of the average for the goal fill rate as you requested I get the output in the last column.  The sum in this case would be a little closer to what I would expect but it appears that some service classes are over-weighted and others  under-weighted as a result.

                 

                Ideally I would like to sum the total line in the “Weighted Target SL” column to be summing the outputs of the calculation which I initially provided and have updated in the second screenshot.  I changed the names slightly as I did a sum on the initial Demand Order Lines field but for all intents and purposes it is the same.

                 

                 

                 

                 

                Brandon Petesch

                Sr. Wholesale Supply Planner

                O: (414) 343-7418 C: (414) 218-9847