Calculate the Running Average in SSRS

This SSRS article shows how to calculate the running average for a table report with and without grouping using a RunningValue function. To do so, right-click on the Datasets folder to create a new DataSet.

The below code shows the data set that we use for this example.

The Sql query that we used for this SSRS example is:

SELECT * FROM Employee

How to Calculate the Running Average in SSRS?

We have designed a simple table report of Employee sales and formatted the font and colors. Under the Row group section, click the down arrow beside the details and select Add Group and Parent Group.

Let me choose the Occupation column as the group by option and click OK. Now the complete employee table will grouped by their occupation. Right-click on the sales column, choose the insert column and then select the right option to create a new column on the right side of the sales.

Add a new column

We named the header Running Avg. Next, right-click on the empty cell below the header and choose an expression to calculate the running average.

Write the below expression. The SSRS reporting service has a RunningValue function to calculate the running average, which accepts a numeric column, an aggregate function, and the dataset name. Here, we have chosen avg aggregate method to find the average.

=RunningValue(Fields!Sales.Value, Avg, "DataSet1")
Expression to Calculate Running Average in SSRS table report

If you go to the report preview now, you can see the running average from top to bottom.

Calculate Running Average in SSRS table report preview

To calculate the running average for the SSRS group report, we must replace the data set name with the group name in the expression. So, replace DataSet1 with Occupation (group name).

=RunningValue(Fields!Sales.Value, Avg, "Occupation")

Now you can see the running average calculated based on the occupation group. So for each group, the running average value restarts by reporting services.

Calculate Running Average in SSRS grouped table report preview