DAX Tutorial: Running Total or Cumulative Total of Non Date Columns in Power BI.

One common requirement in data analysis is to calculate the running total or cumulative total of non-date columns. In this tutorial, we will explore how to achieve this using DAX in Power BI.

Understanding the Scenario

Before diving into the solution, let’s understand the scenario we are trying to address. Imagine you have a dataset that contains sales data for different products. Each row represents a sale and includes columns such as Product, Quantity, and Sales Amount. You want to calculate the running total of the Sales Amount for each product.

Creating a Running Total Column

To calculate the running total, we need to create a new calculated column in Power BI. Open your Power BI Desktop and load the dataset that contains the sales data.

1. In the “Fields” pane, right-click on the table that contains the sales data and select “New Column”.

2. In the formula bar, enter the following DAX expression:

Running Total = 
CALCULATE(
    SUM(Sales[Sales Amount]),
    FILTER(
        ALL(Sales),
        Sales[Product] = EARLIER(Sales[Product]) &&
        Sales[Date] <= EARLIER(Sales[Date])
    )
)

3. Press Enter to create the new column.

The DAX expression uses the CALCULATE function to calculate the sum of the Sales Amount column. The FILTER function is used to filter the rows based on the product and date conditions. The EARLIER function is used to refer to the current row context and compare it with the previous rows.

Using the Running Total Column

Once the running total column is created, you can use it in your Power BI visuals to analyze the data. Here are a few examples:

Line Chart

Create a line chart with the Date on the x-axis and the Running Total on the y-axis. This will allow you to visualize the cumulative sales over time for each product.

Table Visualization

Add a table visualization and include the Product, Date, Sales Amount, and Running Total columns. This will provide a detailed view of the sales data, including the running total for each product.

Card Visualization

Create a card visualization and display the Running Total for a specific product. This can be useful to quickly see the cumulative sales for a particular product.

Considerations and Limitations

While calculating the running total using DAX in Power BI is a powerful feature, there are a few considerations and limitations to keep in mind:

1. Performance: Calculating the running total for a large dataset can impact the performance of your Power BI report. It is recommended to optimize the DAX expressions and use appropriate indexing techniques to improve performance.

2. Sorting: The running total calculation relies on the order of the rows in the dataset. Make sure your data is sorted correctly before applying the running total calculation.

3. Filtering: If you apply any filters to your visuals, the running total will be recalculated based on the filtered data. This can affect the accuracy of the running total calculation.

Conclusion

In this tutorial, we explored how to calculate the running total or cumulative total of non-date columns using DAX in Power BI. By creating a calculated column and using the appropriate DAX expressions, you can analyze and visualize the cumulative values in your data. Keep in mind the considerations and limitations mentioned to ensure accurate results and optimal performance.

Leave a Comment