I've got data that looks like this:
year channel discount sales
2015 1 3.00 20,000
2015 1 3.00 23,000
2015 2 3.00 67,000
2015 1 6.00 80,000
2015 1 9.00 40,000
2016 1 4.00 98,000
2016 1 7.00 34,000
2016 1 7.00 15,000
2016 2 7.00 45,000
2016 1 10.00 60,000
2017 1 4.50 54,000
2017 1 7.50 60,000
2017 1 10.50 50,000
2017 2 10.50 54,568
I would like to use power pivot to group by year
and calculate the weighted sales like this: The discount should be multiplied by the sales associated with it, then divided by the total sales per year.
In Powerpivot, I calculate my total sales in the column by
TOTAL_SALES_YEAR=CALCULATE(SUM([sales]), ALLEXCEPT(Sheet1,Sheet1[year]))
and my weighted sales as WEIGHTED_SALES=[discount]*[sales]/[TOTAL_SALES_YEAR]
.
Now, however, I would like for my total sales to react to filters - whether channel 1, channel 2, or both are selected.
All channels: Subtotals are calculated correctly
Filtered channel: Subtotals have not changed
How can I go about this? Thank you.