How to Show Values as a Percentage in Power BI (Total, Row, Column, Group)

A regional sales VP I worked with looked at a Power BI matrix full of dollar amounts and asked the question that comes up in almost every report review: “But what share is that?” Converting the numbers to percentages took a right-click, and a few harder cases, like each product’s share of its own category, took a short measure.

In this tutorial, I’ll show you the built-in Show value as option, then DAX measures for percent of total, row and column totals, subtotals and groups, and how to format the results. The examples use the Superstore-style sample data many Power BI users know.

Quick answer: In a matrix or table, select the dropdown arrow on the field in Values, choose Show value as, and pick Percent of grand total, Percent of column total or Percent of row total (the last two are matrix only). For more control, create a measure such as DIVIDE(SUM(Orders[Sales]), CALCULATE(SUM(Orders[Sales]), ALLSELECTED(Orders))) and set its format to Percentage.

The quickest way: Show value as

Every numeric field in a visual’s Values well has a Show value as option, with no DAX needed:

Option	Shows each value as a share of	Visuals
No calculation	Nothing; the raw value (default)	All
Percent of grand total	The overall total	Matrix, table and charts
Percent of column total	Its column’s total	Matrix only
Percent of row total	Its row’s total	Matrix only

Here, a matrix has Category on Rows, Segment on Columns and Profit in Values:

Select the arrow next to Profit in the Values well, hover over Show value as and choose Percent of column total:

Each segment’s column now adds up to 100%:

You can add the same field to Values twice, one copy as the raw value and one as a percentage, to show both side by side. If the column and row options are grayed out, you’re using a table visual; switch to a matrix.

Percentages in a chart

Charts can show a share too. A 100% stacked column chart with Segment on the X-axis, Sub-Category in Legend and Sales on the Y-axis shows each sub-category’s share of each segment:

Turn on Data labels under Format visual > Visual to see the percentages:

For other charts, the Values field’s Show value as > Percent of grand total works the same way.

Percent in a matrix with dates

With Category on Rows, Segment and Order Date (year and month) on Columns, and Profit in Values, Show value as works across the hierarchy:

Expand the columns with the +/- icons or the Expand all button in the visual header to see each month’s share:

Format a measure as a percentage

A ratio such as 0.6383 shows as 63.83% once you format it as a percentage. This table has wins, losses and ties for each coach:

Create a measure with Home > New measure. DIVIDE is safer than / because it returns blank instead of an error when the denominator is zero:

Winning % =
DIVIDE (
    SUM ( Coaches[Wins] ),
    SUM ( Coaches[Wins] ) + SUM ( Coaches[Losses] ) + SUM ( Coaches[Ties] )
)

With the measure selected, go to Measure tools, set Format to Percentage and choose the decimal places:

Only format ratios this way. Formatting a raw amount column, such as Sales, as a percentage multiplies it by 100, so $1,250 shows as 125000%. Also remember that 0.9 is 90%, not 9%.

Percent with no decimals

Set the decimal places to 0 next to the Format box, or use the custom format 0%, which shows 0.156 as 16%. Here, a cost share shows as 0.51 before formatting:

Percent of grand total with a measure

For a measure you can reuse in any visual, divide by the total with the filters on one column removed. For each customer’s share of sales:

% of Grand Total =
DIVIDE (
    SUM ( Orders[Sales] ),
    CALCULATE ( SUM ( Orders[Sales] ), ALL ( Orders[Customer Name] ) )
)

ALL(Orders[Customer Name]) ignores the customer filter only, so if a slicer filters by segment, you get each customer’s share of that segment. To ignore every filter, use ALL(Orders); to respect slicers but ignore the visual’s rows, use ALLSELECTED(Orders).

Across two related tables

When the product names are in a separate table related to the sales table, remove the filter on the product name:

Total Sales = SUM ( 'Sales data'[Sales] )
All Product Sales = CALCULATE ( [Total Sales], ALL ( 'Product table'[Product name] ) )
% of Total = DIVIDE ( [Total Sales], [All Product Sales] )

This only works if the two tables have a relationship.

Percent of row total with a measure

Percent of row total is easiest with Show value as. If you need a measure, remove the filter on the column field only, here the team, so each row’s cells add up to 100%:

% of Row Total =
DIVIDE (
    COUNT ( 'Team manage'[Tickets] ),
    CALCULATE ( COUNT ( 'Team manage'[Tickets] ), REMOVEFILTERS ( 'Team manage'[Team] ) )
)

A common mistake is ALL('Team manage') in the denominator. That removes every filter, so you get the percent of the grand total, not of the row.

Percent of a subtotal

This production table has the cost of material and machine for each department and product:

For each department’s share within its product, keep only the product filter with ALLEXCEPT:

% Cost of Material =
DIVIDE (
    SUM ( Production[Cost of material] ),
    CALCULATE ( SUM ( Production[Cost of material] ), ALLEXCEPT ( Production, Production[Production] ) )
)

The measures in the screenshots wrap the numerator in an extra CALCULATE and use /; the DIVIDE version above gives the same result and avoids divide-by-zero errors. ALLEXCEPT also removes slicer filters on other Production columns, so use ALLSELECTED if you want slicers to shrink the total.

Percent of the visible column total

To show each region’s share of the regions currently shown, use ALLSELECTED, which respects slicers and filters:

Total Revenue = SUM ( 'Table'[Revenue] )
% of Region Revenue = DIVIDE ( [Total Revenue], CALCULATE ( [Total Revenue], ALLSELECTED ( 'Table' ) ) )

The screenshot uses a SUMX version of the same idea; the CALCULATE version is simpler and faster.

Percent of total with a filter

To show one company’s share within its region, even when the visual is filtered to that company, keep only the region filter in the denominator:

% Share Value =
VAR Volume = SUM ( 'Table 2'[Value] )
VAR AllVolume = CALCULATE ( SUM ( 'Table 2'[Value] ), ALLEXCEPT ( 'Table 2', 'Table 2'[Region] ) )
RETURN DIVIDE ( Volume, AllVolume )

Percent of a group at each level

With a Market > Category > Item hierarchy, you might want each item as a share of its category, each category as a share of its market, and each market as a share of the total:

ISINSCOPE tells the measure which level it’s on, so one measure handles all three:

Group % =
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Sales Data'[Item] ),
        DIVIDE ( SUM ( 'Sales Data'[Sales] ), CALCULATE ( SUM ( 'Sales Data'[Sales] ), ALLSELECTED ( 'Sales Data'[Item] ) ) ),
    ISINSCOPE ( 'Sales Data'[Category] ),
        DIVIDE ( SUM ( 'Sales Data'[Sales] ), CALCULATE ( SUM ( 'Sales Data'[Sales] ), ALLSELECTED ( 'Sales Data'[Category] ) ) ),
    ISINSCOPE ( 'Sales Data'[Market] ),
        DIVIDE ( SUM ( 'Sales Data'[Sales] ), CALCULATE ( SUM ( 'Sales Data'[Sales] ), ALLSELECTED ( 'Sales Data'[Market] ) ) ),
    1
)

Some tutorials use a trick that checks whether a level’s percentage equals 1 to decide the level. It breaks whenever a group has a single item, so use ISINSCOPE instead.

No DAX: visual calculations

In a matrix, select New visual calculation and use the Percent of parent or Percent of grand total template. They create formulas such as DIVIDE([Sales], COLLAPSE([Sales], ROWS)) that work only in that visual.

Show a percentage on a card

A card has no rows or columns, so a percent-of-total measure on its own always shows 100%. Put the measure, not the Sales column, on the card, and add a slicer, for example on Customer Name, so the card shows the selected customer’s share. To see the share next to the total, use a multi-row card or a table with both the amount and the percentage.

These Power BI tutorials go well with percentages:

• Hide or Remove Column Headers on Power BI Matrix Visual
• Display Multiple Rows in Matrix Without Hierarchy in Power BI
• How to Show All Values in Power BI Matrix [Not Just First Value]
• Power bi show items with no data
• Power BI average function with Examples
• Power BI Measure multiply with examples

Frequently Asked Questions