When creating reports, percentages are usually a preferred way of displaying values. This is a common practice for various reasons. Percentages provide a standardized and easily interpretable way to represent data, regardless of the scale or magnitude of the underlying values.
This uniformity enables quick comparisons between different data points and makes it simpler for users of the report to grasp the relative significance of each item in a dataset.
Percentages are efficient for working with proportions, ratios, or divisions of a whole as they aid with the understanding of data composition and distribution within a specific context. They also make complex data more accessible to a broader audience.
Overall, percentages play a pivotal role in facilitating well-informed decisions and nurturing data-driven strategies, enabling analysts and decision-makers to extract precise and actionable insights with clarity.
This post will delve into the different approaches within Power BI that enable you to effortlessly convert your values into percentages.
Display Values as Percentages in Power Query
Power Query is an engine for carrying out data manipulation and transformation. With this tool, you have the ability to perform a wide range of transformations on your dataset, including converting values into percentages.
While there are various approaches to changing a column’s datatype to percentages, this method offers simplicity and efficiency.
All you need to do is locate the column you want to convert into percentages, click on the datatype icon beside the column name, and select Percentages.
When you format a column as a percentage, Power Query automatically multiplies the values by 100. Take note of this distinction in the image above.
If you want to add percentage formatting to more than one column, follow these steps.
- Select the columns you want to format as percentages by holding the Ctrl button while you click on the column headers.
- Go to the Transform menu, click on the Data Type dropdown, and select Percentage.
Display Values as Percentages in the Data View Tab
The Data View tab in Power BI offers a comprehensive and organized display of all the tables in your dataset. In this tab, you can effortlessly perform various transformation tasks, including converting formats to percentages.
The process involved is identical to how you would do it in Power Query. You simply select the column whose format you want to change, go to the Table Tools tab and change the format to Percentage using the Format dropdown options.
Display Values as Percentages in a Measure
When creating measures, you can easily specify a percentage format by clicking on the % option in the Formatting group.
Display Values as Percentages in a Visual
Aside from formatting measures as percentages, you can also make measures formatted as numbers to display as percentages in a visual. This doesn’t change the underlying number format for the measure, it only affects how the measure is displayed in the visual.
After adding the measure to the visual, select the dropdown arrow in the measure well and click on Show value as. Select the Percent of grand total option from the contextual menu.
When you do this, the values will be displayed as percentages in the visual as it is in the above image. This configuration works on other visuals like bar and column charts.
For these charts however, you will need to enable the display of Data labels in the Formal visual tab so you can see the values on the chart.
Display Values as Percentages in the Model View
Another place where you can change the format of a column to percentages is in the Model view. Much like the Data view tab, the Model view tab contains all the tables and columns of the dataset imported into Power BI.
The Model view presents a visual representation of table relationships, data fields, and calculations, which facilitates an understanding and control of the data structure. Aside from this, you can create and modify relationships, establish hierarchies using a user-friendly drag-and-drop interface.
Additionally, it allows you to design and manage the underlying data model for reports and visualizations.
To format values as percentages in the Model view, click on the column you want to format and go to the Properties pane. Then in the Formats option, select Percentage from the dropdown menu.
Display Values as Percentages with DAX
DAX’s FORMAT function offers an additional way to display values as percentages. The FORMAT function simply allows you to apply any number format you want, including percentages. You can see it as the DAX equivalent of using the Measure tools tab.
However, when employing this method to create a measure, it’s important to note that the resulting values will be in text format, limiting their use to card visuals. Consequently, the measure serves no further purpose in calculations.
The FORMAT function’s syntax uses three parameters:
FILTER ( <Value>, <Format>, [<LocaleName>] )
<Value>– refers to the number or measure that returns a scalar value you want to format.
<Format>– the format type you want to apply to the Value.
<LocaleName>– refers to the locale name that will determine the formatting style. This is an optional parameter and will use the default locale setting in Power Bi when omitted.
Profit/Revenue = FORMAT( ([Total Sales] - [Cost]) / [Total Sales], "Percent" )
If you want to determine a proportion, such as the Profit to Revenue ratio shown in the above visual, you can use this FORMAT syntax to create the measure and present it as a percentage.
In the syntax, the
([Total Sales] - [Cost]) / [Total Sales]) returns the proportion while the “Percent” formats the result as a percentage.
When you add it to a visual, it looks just like every other measure when created without the FORMAT function.
Viewing values as percentages in Power BI isn’t just a simple formatting choice. It’s a powerful technique that boosts data clarity and helps you make informed decisions.
Whether you’re showing market share, budget allocation, or any other data-driven metric, percentages offer a standardized and easy way to communicate information effectively.
This approach goes beyond raw numbers, allowing report users to quickly understand the importance of data points and make accurate comparisons. By using percentages in your Power BI visualizations, you can turn complex datasets into actionable insights.