none
Conditional sum of columns RRS feed

  • Question

  • Hi all

    I have a data set that has 5 columns with values, e.g. sales week 1, sales week 2, ... , sales week 5.

    My user would like to look at the sum of the sales weeks in a report, but needs flexibility on which weeks to add up. E.g. in one moment, he may want to look at the sum of the first 2 weeks, in another moment the sum of the first 3 weeks, and in again another moment the sum of all 5 weeks.

    My data set is created in power query and loaded into a power pivot data model.

    Would anyone have any idea on how to create this dynamic sum?

    I was thinking of having a cell in Excel that would have the number of weeks to add up, load this number into the data model as a column, and then create a conditional calculation (if =1 then take just week 1, if = 2, then sum week 1 + week 2, etc) but not sure if this is the best approach.

    Thanks

    Bart

    Wednesday, March 14, 2018 10:47 PM

Answers

All replies

  • Suggested approach:

    1) Unpivot the week columns in Power Query, renaming the resulting Attribute column to "Week", and the resulting Value column to "Sales."

    2) Load the data to Power Pivot

    3) Create a measure named SalesAmount with the expression SUM(<tablename>[Sales])

    4) Create a pivot table putting Week in Rows and SalesAmount in Values

    5) Create a week slicer to allow the user to select the weeks to sum as desired.

    Thursday, March 15, 2018 1:35 AM
  • hi Colin

    Thanks for your reply. Reading it, i realize i simplified my problem statement a bit too much. 

    Here's a second attempt:

    An actual row in my data set has a product as it's key, and a number of columns all with measures, similar to this screenshot:

    Applying your suggested method won't work as having weeks in the rows would also duplicate the forecast, sales and backorders values.

    The calculation we have shows backorders minus week 1 expected qty minus week 2 expected qty minus week 3 expected qty. The result is that with the expected qtys we can cover the backorders.

    The flexibility we are looking for, is to see if the backorders could also be covered considering only 1 week of expected qty, or 2. So what I'm looking for is a way to adjust the calculation based on the number of weeks we want to consider, without having to update formulas. E.g. if the user could select the number of weeks to consider in a cell, or through a slicer, that would be great.

    Hope my request is clear, and look forward to your thoughts.

    Bart

    Thursday, March 15, 2018 9:21 PM
  • Hi Bart,

    I'm not sure that I'm understanding your requirement. Could you explain why something like the following wouldn't work?

    Saturday, March 17, 2018 5:50 PM