Table of Contents
The process for calculating the average by month in Power BI involves first selecting the data source, such as a spreadsheet or database, and importing it into the Power BI software. Then, the data is organized and filtered by month using the appropriate date function. Next, the average function is applied to the desired numerical data, such as sales or revenue, within each month. This results in a new column or measure that displays the average value for each month. Finally, the data can be visualized in a chart or table to show the average values for each 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: