In this Power bi tutorial, we will discuss power bi show value as percentage. And also we will discuss the below points:
- Power bi show value as percentage
- Power bi show value as percentage format
- Power bi show value as percentage of row total
- Power bi show value as percentage of column total
- Power bi show value as percentage in matrix
- Power bi show value as percentage of grand total
- Power bi show value as percentage of total
- Power bi show value as percentage of subtotal
- Power bi show card value as percentage
- Power bi show value as percentage no decimal
- Power bi measure percentage column total
- Power bi measure percentage of total with filter
- Power bi measure percentage of group
Power bi show value as a percentage
Here we will see power bi show value as a percentage in power bi.
We are using a 100% stacked column chart to show value as a percentage when we hover over the stacked column chart it will show the percentage.
- Open your power bi desktop. Load the data using get data. Get the same data.
- Select 100% stacked column chart visual from the visualization pane.
- In the axis field, drag and drop the segment column from the field pane.
- In the legend field, drag and drop the sub-category column from the field pane.
- In the value field, drag and drop the sales column from the field pane.
- Now we will change the value of the sales column to percentage in power bi report.
- So click on the sales column in the field pane, then column tools will open in the ribbon.
- In the formatting section, click on the format drop-down and select percentage.
- Now when you hover over the visual you can see the percentage, and also turn on the data label from the fromatting section in the visualization pane.
- In the below screenshot you can see the power bi show value as percentage.
Read Power bi show items with no data
Power bi show value as percentage format
Here we will see how to format calculated measure as percentage in power bi.
Here we are using the below sample data to show the value of measure as percentage format.
Now we will create a measure that will calculate the winning percentage.
- Click on the new measure from the ribbon in power bi desktop.
- Then write the Dax formula:
Winning % = SUM([Win ]) / (SUM([Win ])+SUM([Losses])+SUM([Ties]))
- Now navigate to the data model in power bi, Click on the measure in the field pane.
- Measure tool will open, change the format to percentage in the formatting section.
Now, I have created a table visualization, then I have added coach, win, loose, tie, and winning percentage(measure) in the value field.
In the below screenshot you can see the power bi show values as percentage format.
Read How to create a Power BI Dashboard in Microsoft teams
Power bi show value as percentage of row total
Here we will see how to show value as percentage of row total in power bi.
Here we are using the below sample data to show value as percentage of row total in power bi desktop.
We will use the matrix visual to show the percentage of row total, to find the % of row total for ticket in power bi we will use the measure.
- To create the measure, Click on the new measure from the ribbon in power bi desktop.
- Then write the Dax formula to calculate the measure:
% of Row Total = COUNT([Tickets])/CALCULATE(COUNT('Team manage'[Tickets]),ALL('Team manage'))
- Here i have create a matrix visual, where in the Axis field i have added resolved months, and then in the legend field i have added Team column and in values field % row total.
- And i have turn off the stepped layout from formatting pane.
- In the below screenshot you can see the power bi show value as percentage of row total.
Read Power bi sum group by multiple columns
Power bi show value as percentage of column total
Here we will see how to show value as percentage of column total in matrix visual.
- In power bi desktop select the matrix visual from the visualization pane.
- In the Rows field, drag and drop the category field from the field pane
- In the column field, drag and drop the segment field from the field pane.
- In the value field, drag and drop the profit column from the field panel.
- In the value field Click on the dropdown -> Show value as -> percent column total.
In the below screenshot, you can see the Power bi show value as a percentage of column total.
Read Power bi Date Difference – 8 Different Examples
Power bi show value as percentage in matrix
Here we will see, how to show value as percentage in matrix visual in power bi desktop.
- In power bi desktop, select the matrix visual from the visualization pane.
- In the Row field, drag and drop the Category and segment column from the field pane.
- In the Column field, drag and drop the Order date (year and month) from the field pane.
- In the value field, drag and drop the profit column from the field pane .
- Click on the dropdown arrow and select show value as -> percentage of column total.
- Then click on drill down, to show the month and year both, for each month it will show the percentage of profit.
- In the below screenshot you can see the power bi show value as percentage on matrix.
Read Power bi measure subtract
Power bi show value as percentage of grand total
Here we will see how power bi show values as percentage of grand total of sales in power bi desktop.
We will create a measure which will find the percentage of grand total of sales based on the customer.
- To create a measure, click on the New measure from the ribbon in power bi desktop.
- Then write the measure to find out the percentage of grand total.
- The DAX formula is
% of Grand Total = DIVIDE ( SUM ( Orders[Sales] ), CALCULATE ( SUM ( 'Orders'[Sales] ), ALL ( 'Orders'[Customer Name] ) ))
In the below screenshot you can see the power bi show value as percentage of grand total.
Read Power bi date format
Power bi show value as percentage of total
Here we will see how to show value as percentage of total in power bi.
- We have two table, one is sales table and other is product data table. By using sales table first we will find the total sales.
- Next we will find the percent of sales of all product. To do this we need to devide every single number in Sales by the total. For this we will create a new measure i.e EverySales.
- The every sales measure, reference the total sales inside CALCULATE, and then use the ALL function with the product name, though it can remove filter from the dimension
- Then we will find the percent of total, by deviding total sales by every sales in power bi.
Sales table
Product table
- We will create a measure in sales table to calculate the total sales.
- Click on the measure from the ribbon in power bi desktop.
- Write the Dax formula:
Total sales = SUM('Sales data'[Sales])
- Next we will find the every sales by using measure, for this click on the new measure
- Then write the Dax formula:
Every sales = CALCULATE([Total sales], ALL('Product table'[Product name]))
- Now in the table visualization, I have added Product name column, total sales measure and every sales measure. You can see the every single row has the same value under every sales measure
- Now we will find the percentage of total, click on the new measure from the ribbon.
- Then write the Dax formula:
%ofTotal = DIVIDE([Total sales], [Every sales], 0)
Now add the percentage of the total to the Table visualization. In the below screenshot you can see the power bi show value as percentage of total.
Read Power bi group by date range
Power bi show value as percentage of subtotal
Here we will see how to show values as a percentage of subtotal in Power bi.
We will use the below sample data to find power bi show value as a percentage of subtotal.
We will find the percentage of cost of material, percentage of cost machine, and then we will find the percentage of total cost by creating measure.
Let’s find the percentage of cost of machine in power bi.
- Click on the new measure from the ribbon in power bi.
- Then write the measure, the dax formula is:
%CostofMachine = CALCULATE ( SUM ( Production[Cost of machine] ) ) / CALCULATE ( SUM ( Production[Cost of machine] ), ALLEXCEPT ( Production, Production[Production] ) )
- Then write the Dax formula for cost of material by clicking on the new measure.
- The Dax formula is:
%CostofMaterial = CALCULATE ( SUM ( Production[Cost of material]) ) / CALCULATE ( SUM ( Production[Cost of Material] ), ALLEXCEPT ( Production, Production[Production] ) )
- Now we will find the total cost of material and machine in power bi desktop.
- Click on the new measure then write the Dax formula:
TotalCost = CALCULATE ( SUM ( Production[Cost of Machine] ) ) + CALCULATE ( SUM ( Production[Cost of Material] ) )
- Next we will find the percentage of total cost by using measure.
- Click on the new measure, then write the Dax formula:
%TotalCost = CALCULATE ( [TotalCost] ) / CALCULATE ( [TotalCost], ALLEXCEPT ( Production, Production[Production] ) )
- Now click on the %cost of machine, measure tools will open in the ribbon, change the format of the measure general to percentage.
- Similarly change the percentage of cost of material and percentage of total cost.
In the below screenshot, you can see the Power bi show value as a percentage of subtotal.
Power bi show card value as percentage
Here we will see how to show the value as a percentage in card in power bi.
We will see how to find the percentage of total sales respect to customer by using measure, then we will show the percentage on the card.
- To find the percentage we will create a new measure. Click on the new measure in the ribbon.
- Then write the measure:
% of Total = DIVIDE ( SUM ( Orders[Sales] ), CALCULATE ( SUM ( 'Orders'[Sales] ), ALL ( 'Orders'[Customer Name] ) )
- Select the card visual from the visualization pane, the drag and drop the sales column from the field pane, to the field.
- In the below screenshot you can see the power bi show card value as percentage.
Read Power BI Measure IF with Examples
Power bi show value as percentage no decimal
Here we will see how to show value as percentage no decimal value in the matrix visual in power bi.
We will see how to change the decimal value to a percentage. For e.g, if it is showing 0.9 then we need to change it to 9%.
Below the table, I have calculated the percentage cost of material by using a measure.
% Costof Material = CALCULATE ( SUM ( Production[Cost of material]) ) / CALCULATE ( SUM ( Production[Cost of Material] ), ALLEXCEPT ( Production, Production[Production] ) )
So it is showing the result in decimal, you can see the below screenshot.
- To change the measure from decimal value to percentage, click on the measure.
- The measure tools will open, in the formatting section, change the format to percentage.
- Now in the below screenshot you can see the Power bi show value as percentage no decimal.
Power bi measure percentage column total
Here we will see how to find the total percentage of revenue column based on each region in power bi.
We will use the below sample table to calculate the percentage of revenue column total
- Load the data using get data
- So first, we will create a measure to calculate the Total revenue.
- Click on the new measure from the ribbon.
- Then write the below measure:
Total revenue = SUM('Table'[Revenue])
- Now we will use the total revenue to calculate the percentage of revenue for each region using measure.
- Click on the New measure from the ribbon.
- Then write the below measure:
% of revenue on region = DIVIDE(SUMX('Table', [Total revenue]),
SUMX(ALLSELECTED('Table'), [Total revenue]))
- Now click on the measure in the field pane, measure tool will open in the ribbon.
- Then in the format section, change the format to percentage.
- Now to check the measure, select the table visual from the visualization pane.
- In the value field, drag and drop the Region column, Revenue column and % of revenu on each region measure.
This is an example of a Power bi measure percentage column total.
Power bi measure percentage of total with filter
Here we will see how to calculate the percentage of the total value based on each region and filter by company.
We will use the below sample table to calculate the percentage of the total value and then filter with the company using a measure
- Load the data using get data.
- Now we will create a measure which will calculate the percentage of value.
- Click on the New measure from the ribbon.
- The measure is:
% ShareValue =
VAR Volume =
SUM('Table 2'[Value])
VAR AllVolume =
CALCULATE ( SUM('Table 2'[Value]),ALLEXCEPT('Table 2', 'Table 2'[Region]) )
RETURN
DIVIDE ( Volume, AllVolume )
- Now to check the measure, select the table visual from the visualization pane.
- In the value field, drag and drop the region column, company column, value column, and % ShareValue measure from the field pane.
- In the filter pane expand the company colum select the company name to filter the value.
- Here i have selected Delta and in the below sceenshot you can see te percentage of value for company delta.
Here we saw an example of Power bi measure percentage of total with filter.
Power bi measure percentage of group
Here we will see how to calculate the percentage of sales based on the group using the measure in power bi.
We will use the below sample table to calculate the percentage for multiple levels of groups of sales i.e item, category, and market.
- So first, create 3 measures for the multiple level i.e. 3 measure for item, category and market. Then we will use the if condition to show 3 level of percentage in matrix visual.
- Load the data using get data from the ribbon in power bi.
- So first we will create the measure to calculate the percentage for the smallest level i.e. item.
- Click on the new measure from the ribbon.
- Then write the below measure:
level3 % = DIVIDE(SUM('Sales Data'[Sales]),CALCULATE(SUM('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Item]) ) )
- Create another measure to find the percentage for second level i.e. category.
- Click on the new measure from the ribbon
- Then write the below measure:
level2 % = DIVIDE(SUM('Sales Data'[Sales]),CALCULATE(SUM('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Category]) ) )
- Now we will create another measure to calculate the percentage for 1st level i.e. Market.
- Click on the new measure from the ribbon.
- Then write the below measure:
level1 % = DIVIDE(SUM('Sales Data'[Sales]),CALCULATE(SUM('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Market]) ) )
- So in level1( market) measure the total percentage is 100% or 1, so we will use the if condition to repace this value to its pecentage of previous level or level2( category).
- For this create the new measure for level 2 i.e. Market.
- Click on the new measure from the ribbon.
- Then write the below measure:
Level2 = if([level3 %]=1 , [level2 %],[level3 %])
- Now, we finally create a measure for level1 to show the percentage of each group.
- Click on the new measure from the ribbon.
- Then write the below measure:
group% = if([Level2]= 1,[level1 %] ,[Level2])
- Now to check the measure, select the matrix visual from the visualization pane.
- In the rows field, drag and drop the Market, category and items columns from the field pane.
- In the value field, drag and drop the sales column and group % measure from the field pane.
You may like the following Power BI tutorials:
- How to create a table in Power bi dashboard
- Power BI Aggregate Functions
- Power bi date filter
- Power BI Slicer between two dates
- Microsoft Power BI KPI Visual
- How to create a measure in Power BI
- Power BI Date Function with Examples
In this Power bi tutorial, we learned power bi show value as percentage. And also we discussed the below points:
- Power bi show value as percentage format
- Power bi show value as percentage of row total
- Power bi show value as percentage of column total
- Power bi show value as percentage in matrix
- Power bi show value as percentage of grand total
- Power bi show value as percentage of total
- Power bi show value as percentage of subtotal
- Power bi show card value as percentage
- Power bi show value as percentage no decimal
- Power bi measure percentage column total
- Power bi measure percentage of total with filter
- Power bi measure percentage of group
Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com
Thank you for article! I spent 4 hours to convert values to %