Forum Discussion
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 number) and on products for different stores. The aim is getting a cumulative total that works at product/store level either directly on power query or on a pivot.
The starting point is an appended query with all the different years similar to the following (Running Total column just as an example):
| Store | Product | SLS | STOCK | YEARWEEK | RT |
| AAA | A123 | 0 | 8 | 202101 | 0 |
| AAA | A123 | 3 | 5 | 202103 | 3 |
| AAA | A123 | 5 | 0 | 202104 | 8 |
| AAA | C123 | 2 | 10 | 202101 | 2 |
| AAA | C123 | 3 | 7 | 202102 | 5 |
| BBB | A123 | 1 | 5 | 202102 | 1 |
| BBB | B123 | 1 | 19 | 202104 | 1 |
| BBB | B123 | 4 | 15 | 202105 | 5 |
| BBB | B123 | 6 | 9 | 202203 | 11 |
| CCC | A123 | 0 | 10 | 202101 | 0 |
| CCC | A123 | 1 | 9 | 202102 | 1 |
| CCC | A123 | 4 | 5 | 202103 | 5 |
| CCC | B123 | 6 | 12 | 202104 | 6 |
| CCC | B123 | 2 | 10 | 202105 | 8 |
| CCC | B123 | 0 | 10 | 202106 | 8 |
| CCC | A123 | 1 | 4 | 202105 | 6 |
| CCC | A123 | -1 | 5 | 202106 | 5 |
I have tried DAX options such as the following which works for the SKU but not when I select a specific Store in the Pivot as it still gives me the total cumulative of the SKU (I suppose because of the ALLEXCEPT filter.
= CALCULATE (
SUM ( 'Table'[Product] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Product] ),
'Table'[YEARWEEK] <= MAX ( 'Table'[YEARWEEK] )
)
)
Do you have any idea on how I may fix this?
Thank you!
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
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
SergeiBaklan That's great thank you!
There is one (hopefully) last complication: my real database includes many more columns that are product attributes (i.e. material, color) which I may use as filters when working with the Pivot, is there a way to take this into account in your formula without having to add all the columns into the ALLEXCEPT section?Sorry for some delay. You may use
filterRT := VAR maxW = MAX ( Sales[YEARWEEK] ) RETURN CALCULATE ( SUM ( 'Sales'[SLS] ), VALUES ( 'Sales'[Product] ), 'Sales'[YEARWEEK] <= maxW )For the sample
I added STOCK filtering into previous measure
slsRT := CALCULATE ( SUM ( 'Sales'[SLS] ), FILTER ( ALLEXCEPT ( 'Sales', 'Sales'[Product], Sales[Store], Sales[STOCK] ), 'Sales'[YEARWEEK] <= MAX ( 'Sales'[YEARWEEK] ) ) )both give the same result.
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.
- 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?Alternative for SELECTEDVALUE (only on insiders channels so far) is here https://techcommunity.microsoft.com/t5/excel/pivot-dax-formula-and-slicer/m-p/3815349#M190902
Better if you give the sample which illustrates your text.
