Power BI: How to Calculate Average By Month


To calculate an average value by month in Power BI, you can use the following two steps:

Step 1: Create new column that contains month of date.

Step 2: Use DAX to create new measure that calculates average value by month.

The following example shows how to do this in practice.

Example: How to Calculate Average by Month in Power BI

Suppose we have the following table in Power BI named my_data that contains information about total sales made on various dates by some company:

Suppose we would like to calculate the average value in the Sales column, grouped by month.

To do so, click the Table tools tab along the top ribbon, then click the New column icon:

Then type the following formula into the formula bar:

Month = MONTH('my_data'[Date]) 

This will create a new column named Month that contains the month number for each date:

Next, click the Table tools tab again and then click the New measure icon:

Then type the following formula into the formula bar:

Avg Sales = DIVIDE(SUM('my_data'[Sales]), COUNT('my_data'[Month])) 

This will create a new measure named Avg Sales that contains the average value in the Sales column for each month.

Next, suppose that we would like to visualize the average value of the Sales column for each month.

To do so, we can click the Report view icon on the left side of the screen:

Then, click the icon called Clustered column chart under the Visualizations tab:

Then click and drag the Month field under the X-axis label and drag the Avg Sales measure under the Y-axis label:

This will produce the following chart:

The x-axis displays the unique months and the y-axis displays the average sales value for each month.

If we hover over an individual month, we can see the average sales value for that particular month.

For example, if we hover over month 4 (April) we can see that the average sales value for that month is 12:

Additional Resources

The following tutorials explain how to perform other common tasks in Power BI:

How to Count Number of Occurrences in Power BI
How to Calculate Standard Deviation in Power BI
How to Calculate a Cumulative Sum in Power BI

Leave a Reply

Your email address will not be published. Required fields are marked *