In this blog, we will walk through the step-by-step process of calculating a cumulative sum by Product ID using a DAX measure in Power BI. This technique is particularly useful for tracking running totals in sales, revenue, or other numerical data over time.
Step 1: Import the Sales Table
First, load the Sales table from your data source into Power BI. You can do this by navigating to Home > Get Data, selecting your data source, and loading the required table.
Step 2: Create a Measure for Cumulative Sum
- In the Fields pane, right-click on the Sales table and select New Measure.
- Name the measure (e.g., Product Cumulative Sales).
- Enter the following DAX formula to calculate the cumulative sum:
Product Cumulative Sales =
VAR A = [Sales_] – SUM(Sales[Sales_])
RETURN
SUMX(FILTER(SUMMARIZE(ALL(Sales), Sales[Product ID], "revenue", [Sales_]),[revenue] <= A),[revenue])
- Press Enter to create the measure.
Step 3: Use the Measure in Visuals
Now, you can use the Product Cumulative Sales measure in your reports:
- Add a Table or Line Chart visualization.
- Drag Product ID into the rows/axis.
- Add the Product Cumulative Sales measure to display the running total of sales per Product ID.
Final Output
Once applied, your visual will display cumulative sales per Product ID, helping you analyze sales trends efficiently.
Conclusion
Calculating a cumulative sum using DAX is a powerful way to analyze sales trends over time. This method allows you to track product performance and gain valuable insights from your data in Power BI.
If you have any questions, feel free to ask in the comments! 🚀
Regards
Anmol Malviya | Addend Analytics
Proud to be a Microsoft Fabric community super user
Let's Connect on LinkedIn
Subscribe to my YouTube channel for Microsoft Fabric and Power BI updates.
Super User
