Rolling Average In Power BI Power Query

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to dynamically calculate Rolling Average in Power BI Power Query. Let’s get started.

Statistically, Rolling Average or Running Average is a calculation that analyzes data points by creating a series of averages of different subsets of a comprehensive data set.

In the caption below, we have our data loaded to the PBI Power Query Editor

To calculate the Rolling Average, we need to rename the Changed Type APPLIED STEPS. Therefore:

• Right-click on the Changed Type and renamed as thus: ExtractMyTable. Note, you can decide to provide whatever name that suits you.
• In the General group of the Add Column tab, click on Custom Column.
• In the Custom Column formula dialogue box, type in ExtractMyData (which is renamed named provided earlier

• Click OK.

From the caption below, we have the new custom column added with Table. When you look below the caption, we have values inside the Table

• In the General group of the Add Column tab, click on Index Column drop-down and select From 1

From the caption below, we have the Index Column added to the data

• In the General group of the Add Column tab, click on Custom Column.
• In the Custom Column formula dialogue box, click on Custom and the Total. Note, the [Custom] is the Table extracted while the [Total] is the column we want to perform rolling average on. See the caption below

• Click OK

In the caption below, we have the list custom column. In the lower part of the caption, we have all the values in the Total column as a List

Next, we want to return the subset of the list that we did above. For that we are going to deploy the List.Range M function

• In the General group of the Add Column tab, click on Custom Column.
• In the Custom Column formula dialogue box, execute the M formula: List.Range([Custom.1],0,[Index])

Whereby the [Custom.1] is the List.Range first argument which is List; 0 is the offset; and [Index] is the count of number in the list

• Click OK

From the caption below, we have the subset of the list.

Next:

• In the General group of the Add Column tab, click on Custom Column.
• In the Custom Column dialogue box, provide desired column name
• In the Custom Column formula dialogue box, execute the M formula:

=List.Average([Custom.2])

In the above formula, we deployed List.Average M Function to return the average of List subset

Finally click OK

From the caption above, we successfully calculated the Rolling Average. In Row 1, we have 8970.5 which is the same value with the Total in Row 1.

However, in row 2, we have 5904.25 as the Rolling Average. This simply means that the first 2 values in Total column is summed up and then divided by 2 (8970.5+2838/2); and vise versa.