How To Calculate Running Total In Power Bi

How to Calculate Running Total in Power BI

Calculating running totals in Power BI involves using a combination of DAX (Data Analysis Expressions) functions and measures. Here is a step-by-step guide with examples on how to accomplish this:

1. Create a new measure to hold the running total value. This measure will be updated dynamically based on the selected dimension or filters.
2. Use the CALCULATE function in DAX to apply necessary filters or dimensions to the running total calculation.
3. Use the SUMX function in DAX to iterate over a table and calculate the sum of a specific column.
4. Combine the CALCULATE and SUMX functions to calculate the running total.

Example:

Assume we have a table named “Sales” with the following columns: “Date”, “Product”, and “Sales Amount”. We want to calculate the running total of sales amount over time.

      Running Total = CALCULATE(
         SUMX(
            FILTER(
               ALL('Sales'),
               'Sales'[Date] <= MAX('Sales'[Date])
            ),
            'Sales'[Sales Amount]
         )
      )
   

In the above example, the “Running Total” measure uses the CALCULATE function to dynamically update the calculation based on the selected date. The SUMX function iterates over the “Sales” table, filtering the records where the date is less than or equal to the maximum date in the current context. It then calculates the sum of the “Sales Amount” column for those filtered records.

By adding this measure to your Power BI report, you will be able to see the running total of sales amount over time.