How to convert percentages to decimal columns in Power Bi?

by

Here’s a question from a colleague: I’m working with employee performance data in Power BI, and our ratings are stored as percentages (e.g., 85%). However, for some calculations and visualizations, I need these values as decimals (e.g., 0.85). How can I efficiently convert these percentage values to decimals in Power BI without altering the original data? Any ideas are very appreciated!

Transform percentage values to decimals

Introduction

Power BI offers multiple methods to achieve this conversion, allowing you to maintain data integrity while enabling accurate calculations and visualizations.

Assumed Data Model

We’ll create a table named EmployeePerformance with the following columns – EmployeeID (Text), Name (Text), Department (Text), PerformanceRating (Percentage).

Step-by-Step Instructions

  1. Open your Power BI Desktop and load the EmployeePerformance table.
  2. Create a new measure to convert the percentage to a decimal:
   Performance Decimal = DIVIDE(EmployeePerformance[PerformanceRating], 100)
  1. To use this in visuals, add the new measure to your report canvas.
  2. For a calculated column approach, go to Data view and add a new column:
   Performance Decimal = EmployeePerformance [PerformanceRating] / 100
  1. To handle percentage formatting in Power Query:
    a. Go to Home > Transform Data to open Power Query Editor.
    b. Select the PerformanceRating column.
    c. Go to Transform the select Percentage, and then Percentage to Number.

Real-world Example

Imagine you’re creating a report on employee performance across departments. You want to calculate the average performance and display it as both a percentage and a decimal.

  1. Create two measures:
   Avg Performance % = AVERAGE(EmployeePerformance[PerformanceRating])
   Avg Performance Decimal = AVERAGE(EmployeePerformance[PerformanceRating]) /  100
  1. Use these measures in a card visual to display both representations:
  • Avg Performance %: 87.5%
  • Avg Performance Decimal: 0.875
  1. Create a scatter plot comparing department performance using the decimal values for more precise positioning.

Key Concepts

  • Percentages are essentially decimals multiplied by 100.
  • Dividing by 100 converts a percentage to its decimal equivalent.
  • Measures allow for dynamic calculations, while calculated columns are precalculated.
  • Power Query transformations modify the data before it’s loaded into the model.

Troubleshooting

If your conversion isn’t working as expected – proceed as following:

  1. Start by checking the data type of your percentage column. It should be Decimal Number or Percentage.
  2. Ensuring that your percentage values are stored as numbers (e.g., 85) and not text (e.g., “85%”).
  3. If you are using a measure, verify that it’s being used in the correct context within your visuals.
  4. For Power Query transformations, make sure to apply changes and refresh your data model.
  5. If results seem off by a factor of 100, double-check whether you’re dividing or multiplying by 100.