Dynamic Weighted Averages

[Rick17]

I have a powerpivot table with 2 years of data. I am trying to use a measure function in excel pivot tables to calculate a weighted average of revenue generated weighted by sales volume.

Ultimately My data is split into 4 fields
Sale Date Sales Method Sales Volume Sales Revenue.

I want to be able to change my date filter and have the weighted average sales revenue by sales method automatically recalculate.

I have been able to do this by setting up weights for each Sales revenue compared to the whole in powerpivot, and providing the weighted contribution that will later be summed in the pivot table. The problem is this cannot be filtered differently without changing the powerpivot formulas each time.

In Powerpivot I have a Total Sales By Source Column calculated as:
Calculate(SUM('Sales Data'[Sales Volume]),Filter(Filter(ALL('Salesdata'),'Sales Data'[Sales Method]=Earlier('Sales Data'[Sales Method])),'Sales Data'[Sales Date]>Date(2011,1,1)))

I then calculate the Weighted Revenue Contribution:
'Sales Data'[Sales Volume]/'Sales Data'[Total Sales By Source]*'Sales Data'[Sales Revenue]

This Weighted Revenue Contribution columns is automattically summed up in the pivot table providing me with a weighted average of each source over the period.

The problem is if i choose to change the period I have to change the formulas in powerpivot. Is there a way to accomplish the same thing using a measure or something else that would adjust the data based on the filters I apply in the Pivot Table?

Thanks,

 

[powerpivotpro]

Hi Rick. Have you ever used disconnected slicers?

Try this post as a starting point:

http://www.powerpivotpro.com/2011/09/guest-post-greater-thanless-thanbetween-slicers/

 

[Rick17]

Thanks that is a good post and will be useful setting up the dynamic portion of the report. The part I couldn't find is how to set up the weighted average measure. The issue is the only way I have found to use powerpivot to create a weighted average is the method I posted about previously. The problem is this cannot change the periods over which the average is taken. If I attempt to apply any sort of filter after it, it will sum up the weighted contributions, but the weight at which they are calculated to is based on the hardcoded period.

 

[powerpivotpro]

Hi Rick. Can you take another stab at explaining your desired formula for weighted average? Just in English, like "for the current selections, find the value Y for each foo, then multiply that value Y by value X for, then sum all those up" - something like that.

In the meantime, I have this intuitive hunch that the SUMX funtion will end up factoring into the solution:

http://www.powerpivotpro.com/2010/02/sumx-the-5-point-palm-exploding-fxn-technique/

 

[Rick17]

Ok so essentially what I need to do is take a dataset of 2 years and about 50k records and allow me to find a weighted average revenue value for each of the four sales methods. If I were to do this by hand I would do the Sumproduct of the Revenue and the volume and divide by the sum of the volume, but this needs to be specific to the term.

So in my powerpivot table I have set up helper columns. One calculates the sum total of the volume of all sales for the sales method over a given period, the next column takes the current row and divides it by the sum total column, this provides its weighted contribution to the sales. in the final column I multiply the weight times the revenue, this gives me the weighted revenue of that specific column. Then with the pivot table set to the same period, it adds up all these little pieces and the sum total is the weighted average.

 

[powerpivotpro]

Shoot me an email Rick at the address spelled out in my sig, I will send you a workbook.

 

[Rick17]

We worked out the final solution.

SUMX has a rough limitation, it cannot multiply 2 numbers with decimals together, so it truncates one. Depending upon your data size you may not be able to shift the decimal before performing the operation because it also has a limitation on how large of a multiplication result it can handle.

So, the solution is to add a new helper column in your source data to multiply each Wt Avg Row Item X its volume. In this case, Sales Volume x Sales Revenue. This can be hidden from your pivot table and will still allow you to use it in the new Measure you create.

You will need a measure to use for your Sum of your volume

[cVolume]=Sum('Table'[Sales Volume])

Create your measure "Wt Avg Revenue By Volume"

=SUMX('Table','Table'[HelperColumn])/Calculate('Table'[cVolume],ALLEXCEPT('Table',[Date],'Table'[Product],'Table'[Store]))

Your ALLEXCEPT function will allow you to dynamically filter by whatever criteria you enter. If you do not filter by that criteria it won't use it. So in the case above, I can dynamically change the filter in my pivot table for Date, or Product, or Store, or any combination of them and it will still correctly calculate the weighted avereage base on the same measure.

I have only done preliminary testing, but this appears to work perfectly in all cases I have tested so far. You can probably use SUM instead of SUMX in your Wt Avg calculation , but the formula started with the SUMX so I left it.

 

[powerpivotpro]

I posted an explanation of the simplest version here:

http://www.powerpivotpro.com/2012/05/weighted-averages-another-use-of-sumx/