Calculate Running Total (Rolling Total) in Power BI

Summary

This video tutorial on the Petago YouTube channel explains how to calculate running totals in Power BI using DAX functions. It covers the process of creating a running total measure, visualizing the data in a matrix format, and presenting it in a clustered column chart. By demonstrating key DAX functions such as CALCULATE, SUM, FILTER, and ALL, the video provides step-by-step guidance for users to effectively analyze sales data over time, enhancing their data visualization skills in Power BI.

Takeaways

• 😀 A running total is the cumulative sum of a sequence of numbers, updating with each new entry.
• 😀 In Power BI, you can visualize a running total using matrix and column charts.
• 😀 DAX (Data Analysis Expressions) is the language used in Power BI for calculations and measures.
• 😀 Measures in Power BI are calculated fields used for aggregations like sums and averages.
• 😀 The `CALCULATE` function in DAX modifies the context of a calculation based on specified filters.
• 😀 The `SUM` function returns the total of values in a specified column.
• 😀 The `FILTER` function creates a subset of a table based on defined conditions.
• 😀 The `ALL` function can remove filters from a table or column, allowing calculations across all data.
• 😀 To create a running total measure, you use a combination of `CALCULATE`, `SUM`, and `FILTER` functions.
• 😀 Visualizing data in Power BI can be done through matrix visuals and clustered column charts to display trends over time.

Q & A

What is a running total in Power BI?

A running total is the cumulative sum of a sequence of numbers, which updates by adding the value of a new number to the previous total.

What are the two columns present in the Excel data imported into Power BI?

The two columns are 'Month', which contains different dates, and 'Sales Amount', which contains sales figures for each day.

What is a measure in Power BI?

A measure is a calculated field in Power BI used for performing aggregation calculations like sum, average, and more advanced calculations using DAX.

How do you create a new measure in Power BI?

To create a new measure, go to the toolbar, select 'New Measure' from the calculations group, and then rename it for clarity.

What is the purpose of the CALCULATE function in DAX?

The CALCULATE function evaluates an expression in a context that is modified by specified filters, allowing for dynamic calculations.

What does the SUM function do in DAX?

The SUM function returns the total of all numbers in a specified column.

What is the use of the FILTER function in creating a running total?

The FILTER function creates a subset of data based on specific conditions, which is essential for calculating running totals within a defined context.

What does the ALL function do in DAX?

The ALL function removes any filters applied to a table or column, allowing calculations to consider all rows or values regardless of existing filters.

How is a matrix visual set up to display running totals in Power BI?

A matrix visual is set up by placing the month in the rows and adding both the sales amount and the running total measure in the values section.

What is the process for visualizing the running total in a clustered column chart?

To visualize the running total in a clustered column chart, select the chart type, set the month as the axis, and include both the sales amount and running total in the values.