Forum Discussion

steveborty's avatar
steveborty
Copper Contributor
Sep 10, 2023
Solved

Running Total for different products and stores

Hi everyone!   After a couple of days of trial and error I come to you for some help. I am trying to compute the running total of the sales (SLS) up to a certain week (expressed as year&week numbe...
BI & Data Analysis
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 10, 2023

    steveborty 

    Not sure what is desired output. With

    slsRT:=CALCULATE (
    SUM ( 'Sales'[SLS] ),
    FILTER (
        ALLEXCEPT ( 'Sales', 'Sales'[Product], Sales[Store] ),
        'Sales'[YEARWEEK] <= MAX ( 'Sales'[YEARWEEK] )
    )
)

    it's like

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Sep 10, 2023

steveborty 

To calculate a running total of sales (SLS) by product and store up to a certain week, you can use DAX in Power BI or Excel Power Pivot. You are on the right track with the CALCULATE and ALLEXCEPT functions, but you will need to make sure you also consider the store in your calculation. Here is a modified DAX formula to achieve this:

Running Total = CALCULATE ( SUM ( 'Table'[SLS] ), FILTER ( ALL ( 'Table' ), 'Table'[YEARWEEK] <= MAX ( 'Table'[YEARWEEK] ) && 'Table'[Store] = SELECTEDVALUE ( 'Table'[Store] ) && 'Table'[Product] = SELECTEDVALUE ( 'Table'[Product] ) ) )

This formula calculates the running total of SLS for the selected store and product based on the YEARWEEK field. Here is how it works:

  • CALCULATE(SUM('Table'[SLS]), ...) calculates the sum of SLS within a certain context.
  • FILTER(ALL('Table'), ...) creates a filter context within which we want to calculate the running total.
  • 'Table'[YEARWEEK] <= MAX('Table'[YEARWEEK]) ensures that we sum the values up to the current week.
  • 'Table'[Store] = SELECTEDVALUE('Table'[Store]) and 'Table'[Product] = SELECTEDVALUE('Table'[Product]) filter the data based on the selected store and product in the pivot table or slicer.

By using SELECTEDVALUE, you are making sure that the formula considers the selected store and product in your pivot table or slicer. This way, when you filter by a specific store, the running total will correctly aggregate by product and week within that store.

Now, you can add the "Running Total" measure to your pivot table or slicer, and it should give you the desired cumulative total for the selected store and product.

The text was created with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.

steveborty's avatar
steveborty
Copper Contributor
Sep 10, 2023
Thank you for the suggestion!
I think we're getting closer, but I have two questions:
1) Do you know how can I enable the SELECTEDVALUE function? It does not appear as valid when I write it. Is there a way to use IF(HASONEVALUE instead maybe?
2) Is the formula going to work if I have other columns with additional attributes related to the products such as columns indicating the material etc.? Or am I supposed to insert in the formula all the columns for which I will eventually need to filter my Pivot?

Resources