How to Calculate Percentage in Power BI Matrix

A Power BI matrix is the perfect way to display summarized data, but raw numbers often lack perspective. Seeing $50,000 in sales for a specific product category is useful, but knowing that it represents 25% of your total quarterly sales is a far more powerful insight. This article provides a clear, step-by-step guide to calculating different types of percentages directly within your Power BI matrix using DAX.

Before You Begin: Setting Up a Simple Matrix

To follow along, you need a basic Matrix visual. Let’s assume you have a simple sales dataset with fields like Sales Amount, Product Category, and Region. If you don't have one ready, just imagine a table with these columns. To create the foundation for our percentage calculations, set up your Power BI Matrix like this:

• Rows: Drag Product Category into the Rows field.
• Columns: Drag Region into the Columns field.
• Values: Drag Sales Amount into the Values field.

You should now have a standard matrix showing the total sales amount for each product category broken down by region. It's a good start, but we need to add the percentages to give these numbers some much-needed context.

Why DAX Measures are the Right Tool for the Job

While Power BI has some quick "Show value as" options, these can be limiting. Creating your own Data Analysis Expressions (DAX) measures gives you complete control, reusable logic, and a deeper understanding of how your data works. We'll use DAX to create new measures for our percentage calculations, which you can then add to any report. Don't worry if DAX seems intimidating. The patterns for calculating percentages are simple and reuse the same core functions. We'll break down each formula piece by piece.

Method 1: Calculate Percentage of the Grand Total

This is the most common and straightforward type of percentage calculation. You want to see how each individual value in the matrix (e.g., Electronics sales in the North region) contributes to the overall grand total sales. The business question this answers is: "What percentage of our total company sales came from Clothing in the West region?"

The DAX Formula Explained

The key to this calculation is telling Power BI to divide the sales for the current cell by the total sales across all regions and categories. To do this, we need to remove the filters applied by the matrix's rows and columns. This is where the CALCULATE and ALL functions come in.

• SUM([Sales Amount]): This simply calculates the sales for the current cell, respecting the row and column filters (e.g., just Clothing, just West).
• CALCULATE(SUM([Sales Amount]), ALL('SalesTable')): This calculates the grand total. CALCULATE modifies the context in which a calculation is performed. The ALL('SalesTable') function tells it to remove all filters from the 'SalesTable', giving us the undistracted grand total.

Step-by-Step Instructions

1. In the Report view, right-click on your sales table in the Data pane and select New measure.
2. The formula bar will appear at the top. Enter the following DAX formula. Be sure to replace 'SalesTable'[Sales Amount] and 'SalesTable' with your actual table and column names.

% of Grand Total = 
DIVIDE(
    SUM('SalesTable'[Sales Amount]),
    CALCULATE(
        SUM('SalesTable'[Sales Amount]),
        ALL('SalesTable')
    )
)

Pro-Tip: We're using the DIVIDE() function instead of the standard slash symbol (/). This is a safer practice in DAX as it automatically handles cases where the denominator is zero, preventing "division by zero" errors in your report.

1. With your new measure selected in the Data pane, go to the Measure tools ribbon at the top of the screen. In the Formatting section, click the percentage symbol (%) to format your measure correctly. You can also adjust the number of decimal places here.
2. Drag your new % of Grand Total measure into the Values field of your matrix visual. You will now see the percentage of the grand total right below each sales amount.

Method 2: Calculate Percentage of the Column Total

Sometimes you need a more specific comparison. Instead of comparing to the grand total, you want to see how each product category contributes to its regional total. The business question this answers is: "Within the North region, what percentage of sales did the Electronics category account for?"

The DAX Formula Explained

For this scenario, we need to divide the cell's value by the column’s total. This means we need to remove the filter for the row (Product Category) but keep the filter for the column (Region) active.

The ALLEXCEPT function is perfect for this. It removes all context filters from a table except for the columns you specify.

• SUM('SalesTable'[Sales Amount]): Again, this is the value of the current cell.
• CALCULATE(SUM('SalesTable'[Sales Amount]), ALLEXCEPT('SalesTable', 'SalesTable'[Region])): This calculates the column total. The ALLEXCEPT function tells Power BI to remove all filters from 'SalesTable' except for the filter on the 'Region' column.

Step-by-Step Instructions

1. Create another New measure in your sales table.
2. Enter the following DAX formula, adjusting the table and column names for your data model:

% of Column Total = 
DIVIDE(
    SUM('SalesTable'[Sales Amount]),
    CALCULATE(
        SUM('SalesTable'[Sales Amount]),
        ALLEXCEPT('SalesTable', 'SalesTable'[Region])
    )
)

1. Format the new measure as a percentage using the Measure tools ribbon.
2. Drag the % of Column Total measure into the Values field of your matrix. Now, if you look down any column, the percentages will add up to 100%.

Method 3: Calculate Percentage of the Row Total

Following the same logic, you might want to understand the regional distribution of sales for a single product category. The business question this answers is: "For the Electronics category specifically, what percentage of its sales came from the South region?"

The DAX Formula Explained

This is the mirror image of the column total calculation. We want to remove the context filter from the column (Region) but keep the filter for the row (Product Category).

• SUM('SalesTable'[Sales Amount]): The value of the current cell.
• CALCULATE(SUM('SalesTable'[Sales Amount]), ALLEXCEPT('SalesTable', 'SalesTable'[Product Category])): This calculates the row total. ALLEXCEPT removes all filters from 'SalesTable' except for the 'Product Category' filter currently being applied by the row.

Step-by-Step Instructions

1. Create your third and final New measure for this tutorial.
2. Enter this DAX formula:

% of Row Total = 
DIVIDE(
    SUM('SalesTable'[Sales Amount]),
    CALCULATE(
        SUM('SalesTable'[Sales Amount]),
        ALLEXCEPT('SalesTable', 'SalesTable'[Product Category])
    )
)

1. Format this measure as a percentage.
2. Drag the % of Row Total measure into your Values. If you look across any row, all the new percentages will sum up to 100%.

Final Thoughts

Mastering these simple DAX patterns - using ALL for grand totals and ALLEXCEPT for row or column totals - dramatically improves the insights you can pull from a basic Power BI matrix. By moving beyond raw numbers, you build reports that tell a much clearer story, making it easier for you and your team to spot trends and make data-driven decisions.

While learning DAX is a valuable skill, we know it can feel like a steep climb, especially when you need a quick answer without getting tangled in formulas. At Graphed , we created a tool to eliminate that friction completely. We connect to an expanding catalog of sales and marketing datasets, from Shopify to Salesforce to Google Analytics, allowing teams to create cross-channel reports by asking normal business questions in chat instead of having to rely on complex DAX calculations.