I have created a Power BI report based on ‘Product Sales Information‘ for one client, utilizing various types of visualizations. On that report, the client instructed me to display the average sales amount of products in the matrix visual.
However, by default, the matrix visual displays a sum aggregation of the values, showing the total for each column.
I will show you here how to display the Average instead of the Total in a Matrix visual in Power BI. Below are two examples of it:
- Show Average Instead of Total in Matrix Power BI using Properties.
- Display the Total Row as the Average Row in Power BI Matrix using the DAX function.
Show Average Instead of Total in Matrix Power BI
First, let me show you how to change the matrix values to an average aggregation instead of a Total in Power BI.
Suppose you have created a matrix visual in your Power BI report. By default, it applies the Sum aggregation to the values, displaying the total for the column.
Below, you can see the Matrix visual with ‘Product‘ on Rows and ‘Sales Amount‘ on Values, which displays the total sales amount.
Now, I wanted to display the Average of Sales Amount instead of the Total value from the Sales Amount values.
Follow the steps below:
- On the Power BI Desktop report canvas, select the matrix visual.
- Go to the Visualizations pane and under the Build Visual icon.
- On the Values field, click the drop-down and choose the ‘Average‘ aggregation mode.
Then, you can see that Power BI displays the Average of Sales Amount in the matrix visual.
Output:
This way, you can display the aggregation mode as ‘Average’ instead of ‘Total’ in Power BI Matrix.
Display Average of Total Sales in Power BI Matrix Visual
Here, I will show you how to calculate and display the average of total sales in a Matrix by using Power BI DAX calculations.
Below, you can see the Matrix visual that shows the Total Sum of Sales Amount.
Now, I wanted to calculate the Average of the Sales Amount based on the Total value of the Sum of sales amount.
For this, I will create a New visual calculation.
See the steps below:
- On the Power BI report canvas, click on the “New Visual Calculation” option under the Home tab.
- Then, write the following expression placed in the code below to calculate the average of Sales Amount in Power BI Matrix.
Average = if(ISATLEVEL([Product]),[Sum of Sales Amount], EXPANDALL(AVERAGE([Sum of Sales Amount]),ROWS))- Average – Calculation Name
- ‘Product‘, ‘Sum of Sales Amount‘– Row and Value names
From the expression above, replace ‘Product’ and ‘Sum of Sales Amount’ with the names given in your Matrix.
See: Power BI Measure Examples
- Then, the newly created ‘Average‘ value will be automatically added to the Values section, displaying the average of ‘Total Sales Amount’. Reference the Image below:
In the image above, you can see that the highlighted mark indicates a total of 4,69,901,45, and the average of Total sales will be 78,316.91.
This is how to calculate and display the average instead of the Total in the Power BI Matrix visual.
I hope you can now understand how to set the aggregation value to ‘Average’ in Power BI Matrix. Additionally, you have learned that the entire row is displayed as an average row, along with the Total, in a Power BI matrix.
You can also drop your queries in the comment section placed below.
You may like the following tutorials:
- Turn Off Grand Totals in Power BI Matrix
- Set Column Width in Power BI Matrix Visual
- Sort Column Values in Matrix Visual Power BI
- Display Multiple Rows in Matrix Without Hierarchy in Power BI
After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.
