2 Replies Latest reply on Mar 28, 2016 12:10 PM by Eric Shereda

    How do I create weighted averages that work across different periods of time?

    Eric Shereda

      I have spent most of the morning researching how to use weighted averages within Tableau without much success in finding a solution to my problem. I am very new to Tableau so forgive me if this has already been posted elsewhere. Many of the posts I have reviewed were not able to help me yet.

       

      I have attached an example workbook with a set of data (image below). I am trying to display on a map, the average price margin for December 2015 for 2 stores (or Q2, or 2014, 100 stores eventually). The price margin is in the column labeled 1 and the transaction volume is in the column labeled 2.

      Example.png

      When displaying the average price margin in Tableau, it performs a simple average of all values in column 1 for whatever date range is selected. However, I need to weight average the price margins based on the volume of transactions for each transaction number for any date range chosen.

       

      From the Tableau image below, the simple average price margin for Store A is $0.4877. However, due to a very high volume of transactions during transaction number 20, the weighted average price margin is much higher, calculated to be $0.6328. Similarly, the combined average for both stores must also be weighted so that stores with lower volume are averaged properly. So, how can I easily display the weighted average sales margin on the map, similar to below, in such a way that when a different range of time is selected to be displayed on the map, that the values are weighted and displayed properly. A large portion of my work will depend on weighted averages similar to this so I need a good understanding of the weighting process. I have attached an extract of this workbook as well and appreciate all advice and assistance.

      Example Average.png