How to add percentage in Power BI
Adding percentages in Power BI can be achieved by using measures and custom calculations. Here’s a detailed explanation with examples:
Example 1: Calculating percentage of total sales
Let’s say you have a sales dataset with columns SalesAmount and Category. To calculate the percentage of total sales for each category:
SalesPercentage = DIVIDE(SUM('Table'[SalesAmount]), CALCULATE(SUM('Table'[SalesAmount]), ALL('Table')))
In this example, we use the DIVIDE function to divide the sum of sales for each category by the sum of sales for all categories. The CALCULATE function is used to remove any filters on the table, so we get the total sales.
Example 2: Calculating percentage of grand total
If you want to calculate the percentage of each value relative to the grand total, you can use the following measure:
PercentageOfGrandTotal = DIVIDE(SUM('Table'[Value]), CALCULATE(SUM('Table'[Value]), ALL('Table')))
This measure divides the sum of the ‘Value’ column by the sum of all values in the ‘Value’ column.
Example 3: Displaying percentage format
To display the calculated percentages in a proper percentage format, you can format the measure. Right-click on the measure in the Power BI report, select ‘Format’, and choose ‘Percentage’ format under the ‘Data type’ section. This will display the measure as a percentage.
These are just a few examples of how you can add percentages in Power BI. Depending on your specific scenario and data structure, you may need to adapt the calculations to suit your needs.