How to Create a DAX Running Total by Product and Store in Power Pivot
Question details
User needs a DAX measure that calculates a running total for the current year-week while preserving Product and Store filters in a Power Pivot or Power BI report.
- Product
- Microsoft Power Pivot / Power BI (DAX measures)
- Device & OS
- not provided
- Scenario
- Building a Power Pivot or Power BI report that requires a cumulative sales total for each product and store over time.
- Observed behavior
- The running total should accumulate sales from all earlier year-weeks and continue to respect the selected Product and Store filters.
Confirm that your data model contains a Sales table with SLS, Product, Store, and YEARWEEK columns, and that the Power Pivot or Power BI measure editor is available.
Use an ALLEXCEPT Measure to Keep Product and Store Filters
Use this method when the running total should preserve the current Product and Store selections and accumulate sales by YEARWEEK.
The ALLEXCEPT function removes filters from the Sales table except for Product and Store, so the FILTER condition can compare every YEARWEEK value against the latest YEARWEEK without dropping the product and store context.
In Power BI, select the Sales table in the Data pane and choose New measure. In Power Pivot, open the calculation area below the data and click a blank cell.
Enter: slsRT := CALCULATE(SUM('Sales'[SLS]), FILTER(ALLEXCEPT('Sales','Sales'[Product],'Sales'[Store]), 'Sales'[YEARWEEK] <= MAX('Sales'[YEARWEEK])))
Add Product and Store fields to the report as slicers or rows, then place the measure in a table or chart to verify the cumulative total.
Use ALL and SELECTEDVALUE for Explicit Product and Store Filtering
Use this version when you want to explicitly compare each row to the selected Product and Store values in a single table model.
Use WPS Office as a Free Spreadsheet Alternative for Running Totals and Reports
If DAX in Power Pivot or Power BI feels complex, WPS Office offers a free, lightweight spreadsheet with Excel-compatible formulas, pivot tables, and a familiar interface. You can open your workbooks, create running totals with formulas or pivot tables, and save back to .xlsx without losing data.
- 1. Download and install WPS Office: Get WPS Office from the official WPS site and install the free version on your device.
- 2. Open the Excel workbook in WPS Spreadsheets: Launch WPS Spreadsheets, select Open, and choose the .xlsx report that contains your sales data.
- 3. Recreate the running total with formulas or a pivot table: Use helper columns or pivot tables to maintain Product and Store filters, or apply SUMIFS and cumulative formulas for year-week totals.
Frequently Asked Questions
Why does my DAX running total ignore the Product or Store filter?
Usually the ALL or ALLEXCEPT arguments are removing the wrong columns. For a product-and-store running total, keep both Product and Store inside ALLEXCEPT, or compare Store and Product to SELECTEDVALUE in the FILTER expression.
Can I use this measure in both Excel Power Pivot and Power BI?
Yes. The formula works in both as long as the table and column names in your data model match exactly; replace Sales, SLS, Product, Store, and YEARWEEK with your actual names.
What should I do if my date column is a calendar date instead of YEARWEEK?
Replace YEARWEEK with a date column from a proper calendar table, then use MAX('Calendar'[Date]) and set a relationship to the Sales table so the running total follows chronological order.
Does WPS Office support Power Pivot or DAX measures?
WPS Office does not include the Power Pivot DAX data model. You can open .xlsx files in WPS Spreadsheets and build running totals with formulas or pivot tables, but DAX measures require Excel Power Pivot or Power BI.
