How to convert values to percentages in Power BI

by

A colleague of mine asked how to convert raw sales figures into percentages of total sales for each product category in Power BI. Below is the step-by-step implementation.

Converting whole numbers to percentages in Power BI

In many real-life data analysis scenarios, we are asked to convert certain measure values to a percentage format for easier visualization purposes, especially when developing executive dashboards. In this short tutorial we will walk through the process of transforming sales figures into percentages using Power BI.

For our tutorial we will assume the following very simple data model: SalesData (Table) with columns: Date (Date), Product (Text), Category (Text), Sales (Decimal)

  1. Create a measure for total sales:
   Total Sales = SUM(SalesData[Sales])
  1. Create a measure for sales percentage:
 Sales Percentage = 
DIVIDE(
    SUM(SalesData[Sales]),
    CALCULATE(
        SUM(SalesData[Sales]),
        ALL(SalesData)
    ),
    0
)
  1. Now go ahead and add a visual (e.g., table or matrix) to your report.
  2. Nest step, drag the Category field to the Rows section of your matrix or table.
  3. Add the Sales Percentage measure to the Values section.
  4. The critical step in the tutorial is to format the Sales Percentage measure as a percentage value (rather than as a whole number, decimal etc’):
  • Click on the Sales Percentage measure in the Data section.
  • In the Modeling tab, set the Format to Percentage.
  • Adjust decimal places as needed.

Note: if needed you can export your matrix data to a csv file.

Troubleshooting

Common issues when converting to percentages include:

  1. Blank results: Check for divide by zero errors by adding a fallback value in the DIVIDE function.
  2. Formatting issues: Verify that the measure is formatted as a percentage in the Modeling tab.
  3. Inconsistent results: Confirm that your filter context is appropriate for your analysis needs.

If percentages don’t add up to 100%, double-check your total calculation and ensure you’re not inadvertently filtering out any data.