Recently, I was working on a Power BI matrix visual for a client. They wanted to calculate the percentage of the total in the Matrix visual.
The client had sales data for various products like laptops, keyboards, mouse, and monitors. They wanted a Power BI report that not only showed the sales values but also displayed the percentage of total sales contributed by each product in a matrix visual.
I will demonstrate here how to calculate and display the percentage of the total in the Power BI Matrix visual.
Show Percentage of Total in Power BI Matrix
Let’s see how to display the percentage of the total using Power BI DAX.
For example, consider the Matrix visual below, which displays ‘Product‘ in the Rows field and ‘Sales Amount‘ in the Value field.
Now, I wanted to calculate the percentage of sales revenue generated from each product as a proportion of the total sales amount.
If you are not aware of how to connect a data source and create a Matrix in Power BI, check out How to Create a Matrix in Power BI.
We need to create a measure to calculate the percentage of the total sales in Power BI Matrix.
Begin with the steps below:
- On the Power BI report canvas, as you can see, the matrix visual has Rows and Values field data. Click on the “New Measure” option under the Home tab.
- Then, write the following expression placed in the code below to calculate the percentage of each product from the total sales Amount in Power BI.
- To display the measure value in a percentage format, select ‘Format to Percentage‘ while creating the measure, as shown below.
Percentage of Total = DIVIDE(SUM('Sales Blank Data'[Sales Amount]),CALCULATE(SUM('Sales Blank Data'[Sales Amount]),ALL()))- Percentage of Total – Measure Name
- ‘Sales Blank Data’ – Table Name
- ‘Sales Blank Data'[Sales Amount] – Sales value for each product
The new measure has now been created.
- Then, select the Matrix visual and open the Visualization pane. On to the Value section, drag and drop the created measure [Percentage of Total ] from the Data pane. See the image below:
- The screenshot below displays the Matrix visual with the total percentage of each product in Power BI.
This is how to calculate the Percentage of Total in the Power BI Matrix visual.
In this tutorial, I explained how to calculate the Percentage of Total in the Power BI Matrix visual.
Specifically, we learned how to calculate the percentage of each product from the total sales amount using a matrix visual.
You can also drop your queries in the comment section placed below.
Learn About Power BI Matrix:
- 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.
