2 Replies Latest reply on Oct 4, 2012 11:19 AM by Mark Holtz

    Does anyone know a way to aggregate new or impromptu data by weighted averaging?

    Stephen Bone

      My data ALWAYS has to be weighted when averaged.  I've created calculated fields in which when I update the data the new data fits right into the calculated weighted average fields, that's great.  However, I'd like to add additional data or use impromptu data.  Is there any better way than to go in and create a calculated weighted average for each new field i bring in?  As you might imagine this greatly limits my flexibility.  Would be greatly indebted to anyone who might know a solution to this!

        • 1. Re: Does anyone know a way to aggregate new or impromptu data by weighted averaging?
          Tracy Rodgers

          Hi Stephen,

           

          So you mean you'd like a new weighted average for each new measure that you add to the data? If this is the case, you'll have to create a calculated field for each weighted average, unless you add it to your data.

           

          -Tracy

          • 2. Re: Does anyone know a way to aggregate new or impromptu data by weighted averaging?
            Mark Holtz

            Hi Stephen,

            I'm not sure what method you're describing to get weighted average, so I'm not sure if this will be helpful to you, but we have a similar situation around some of our measures sometimes, and we have a very simple way of doing it.

             

            Say for instance we have a % variance field for Revenue-to-Budget.

            Some clients may be way off budget, but they don't carry as much weight in terms of revenue...

            Client C here is inflating the arithmetic average variance despite its lower relative weight.

             

            4 clients:

             

            ClientRevenueVarianceRevWeightW-Avg Calc
            A$500,0001.9%0.4761900.009047619
            B$200,0006.4%0.1904760.012190476
            C$100,00015.9%0.0952380.015142857
            D$250,000-3.7%0.238095-0.008809524
            0.027571429
            straight average:5.13%
            avg weight by rev:2.76%
            =SUMPRODUCT(B3:B6,C3:C6)/SUM(B3:B6)

             

             

            It's pretty easy to do a weighted average when you have the weighting field present and the weight is a proportion of the sum of the weight field.  In Excel, you'd use SUMPRODUCT (value * weight field value) / SUM(weight field value)

             

            In Tableau, you could make a simple calculated field for weighted average as:

            SUM([Revenue]*[Variance]) / SUM([Revenue])

             

            This would return the weighted average if you pull it into a view and should return the 2.76% in the grand total.