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,
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,