Search code examples
excelpowerpivot

Excel Power Pivot: calculations with filtered subtotal as a column as source for calculated column

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 All channels: Subtotals are calculated correctly

Filtered channel: Subtotals have not changed Filtered channel: Subtotals have not changed

How can I go about this? Thank you.

Solution

  • When you are using the ALLEXCEPT function, you are stripping the filter context for everything except the [year] column. If you want to preserve the [channel] filter context, then you can add it as a column inside the function as well:

    = CALCULATE(SUM([sales]), ALLEXCEPT(Sheet1, Sheet1[year], Sheet1[channel]))

    Another way to do this is instead of specifying what filter context to keep, you can specify which filter context to remove. It looks like you want to remove the [discount] filter context:

    = CALCULATE(SUM([sales]), ALL(Sheet1[discount]))

    Edit: It appears that you did your formulas as calculated columns rather than as measures. If you want the table to be responsive to filters, then you need to use measures.

    Notice in my screenshot that the measures have "fx" next to them and the pivot table columns are not labeled with "Average of" or "Sum of" aggregation descriptions.

    Your weighted sales measure will also have to change a bit to something like this:

    = SUMX(Sheet1, Sheet1[discount]*Sheet1[sales]) / [TotalSales]

    Measures