How to Calculate Percentage of Rows in Power BI

In this Power BI Tutorial, we will discuss how to Calculate Percentage of Rows in Power BI, In addition, we will also see how to calculate and display the Percentage of Rows in Power BI. Also, covered the below-mentioned headings:

• Power BI Calculate the Percentage of the Row total
• Power BI Calculate the Percentage of the subtotal
• Power BI calculates the percentage difference between two rows

Power BI Calculate Percentage of Row

Let us see how we can calculate and display the percentage of rows in the Power Bi report,

In this example, we will use the financials data table, which you can download and use in the Power Bi report. Here, we will calculate the Profit value based on the product and country in Power BI.

• Log in to the Power Bi desktop and use the get data option to load data, once the data has been loaded click on the new measure under the modeling tab and use the below formula to find the profit percentage value based on the product and country.

% of Row Total = SUM(financials[Profit])/ CALCULATE(SUM(financials[Profit]), ALLEXCEPT(financials,financials[Country]))

Where,

1. % of Row Total = Measure Name
2. financials = Table Name
3. Profit, Country = Column Names

• Select the Matrix visual from visualizations and drag-drop the Country field in the row section, and the product category in the column section.
• In the value section, drag-drop the created % of the row total measure value as highlighted below:

• Select the measure and choose Measure tools -> Percentage as highlighted below to change the measure format to percentage.
• The below screenshot displays the matrix visual of the percentage of the profit value based on the country and product in the Power BI report.

This is how to calculate and display the percentage of rows in the Power Bi report.

Percentage of Parent Row total in Power BI

Here we will see how to calculate the percentage of parent row total in Power BI,

In this example, we will find the percentage value for the COGS with the parent row total in Power Bi

Load the data into the Power Bi desktop and click on the new measure option and apply the below formula to calculate the percentage of parent row total.

% of parent row = DIVIDE( CALCULATE ( SUM ( financials[COGS] ) ),
    CALCULATE ( SUM (financials[COGS] ), ALL ( financials[Product category]) ),
    0)

Where,

1. % of parent row = Measure Name
2. financials = Table Name
3. COGS, Product category = Column Names

• Select the Matrix visual from visualizations and drag-drop the Country and Product Category field in the row section to calculate the parent row percentage value.
• In the value section, drag-drop the created % of the parent row measure value as shown below:

• To change the measure format to percentage, select measure, Measure tools -> Percentage as displayed below:
• The screenshot below displays the matrix visual of the percentage of parent rows total as highlighted based on the country and product in the Power BI report.

This is how to calculate the percentage of parent row total in Power BI.

Read How To Remove Filter From Power BI DAX

Percentage of Row total in Power Bi

Let us see how we can calculate the percentage of row total using the Power Bi DAX in Power Bi,

In this example, we will calculate the sales percentage value row total based on the product category in Power Bi.

Load data using the get data option into the Power Bi, select the new measure option, and write the below formula to find the percentage of row total in power Bi.

% of table row = 
DIVIDE (
    SUM (financials[ Sales]),
    CALCULATE ( SUM ( financials[ Sales] ), ALLSELECTED (financials[Product category] ) ),
    0
)

Where,

1. % of table row = Measure Name
2. financials = Table Name
3. Sales, Product category = Column Names

• Choose the Matrix visual from visualizations and drag-drop the Product Category field in the row section.
• In the value section, drag-drop the created % of the table row measure value as shown below:

• To display the measured value in the percentage format, select measure, Measure tools -> Percentage as displayed below:
• The screenshot below displays the matrix visual of the percentage of rows total as highlighted based on the product in the Power BI report.

This is how to calculate the percentage of the row total using the Power Bi DAX.

Power BI Calculate the Percentage of the subtotal

Here we will see how to calculate the percentage of the subtotal in Power Bi,

In this example, we will calculate the subtotal value percentage for the units sold based on the product category in Power BI.

Open the desktop and load data into it, click on the new measure under the modeling tab, and use the below formula to find the total units sold value.

Total Units = SUM(financials[Units Sold]) 

Where,

1. Total Units = Measure Name
2. financials = Table Name
3. Units Sold= Column Name

In the same way, select a new measure and use the below formula to calculate the total Units Sold percent value.

Unit Sold % = 
VAR UnitSold = [Total Units]
VAR AllProducts =
CALCULATE (
   [Total Units],
    ALL (financials[Product category])
)
VAR Result = DIVIDE ( UnitSold, AllProducts )
RETURN Result

Where,

1. Unit Sold % = Measure Name
2. UnitSold, All Products, Result = Variable Names
3. Total Sales = Existing measure
4. financials = Table Name
5. Product category = Column Name

• Select the matrix visual drag-drop the product category field in the row section, In the values section drag and drop the units sold and units sold % value as highlighted below:

• Select measure – > measure tool -> Percentage to display the percentage value.
• The screenshot below displays the subtotal value percentage for the units sold based on the product category in Power BI.

This is how to calculate the percentage of the subtotal in Power Bi.

Read Subtraction in Power bi using DAX

Power BI calculates the percentage difference between two rows

Here we will see how to calculate the percentage difference between two rows in Power BI,

In this example, First, we will calculate the sale price variation difference for the sale price field presented in the financials table data. Later, we will calculate the percentage of the sale price variation.

• Log in to the power bi desktop, load data and click on the transform data option, it will redirect to the power query editor.
• Under the Add column option, expand the index column and add two index columns, one from 0 indexes and another one from 1 index as below, and click the close and apply the option to save the changes.

= Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type)

Under the Modelling tab, select the new column option and apply the below formula to calculate the sale price variation.

SalePriceVariation = var diff= financials[Sale Price] - CALCULATE(SUM(financials[Sale Price]),FILTER(financials, financials[Index.1]=EARLIER(financials[Index]))) 
return IF(diff = financials[Sale Price],0,diff)

Where,

1. SalePriceVariation = New Column
2. diff = Variable Name
3. financials = Table Name
4. Index.1, Index, Sale Price = Existing Columns

In the below screenshot, we can see the sale price variation difference based on the condition applied.

select the new measure option, and applies the below formula to calculate the percentage difference of sale price variation using Power Bi divide function.

% difference = DIVIDE(SUM(financials[SalePrice]),SUM(financials[SalePriceVariation]),BLANK())

Where,

1. % difference = New measure name
2. financials = Table Name
3. SalePrice, SalePriceVariation = Existing Column names

• Select the Matrix visual from visualizations and drag-drop the Product category field in the row section.
• In the value section, drag-drop the created % difference measure value as highlighted below:

• Select the measure and choose Measure tools -> Percentage as highlighted below to change the measure format to percentage.
• The below screenshot displays the matrix visual of the difference between two rows based on the Sale Price in the Power BI report.

This is how to calculate the percentage difference between two rows in Power BI.

In this Power BI Tutorial, we have discussed how to Calculate Percentage of Rows in Power BI, In addition, we also saw how to calculate and display the Percentage of Rows in Power BI. Also, we covered the below-mentioned headings:

• Power BI Calculate the Percentage of the Row total
• Power BI Calculate the Percentage of the subtotal
• Power BI calculates the percentage difference between two rows

You may like the following Power BI tutorials:

• How to Calculate Percentage of Two Columns in Power BI
• Power BI Percentage of Total by Month
• Power BI Percentage of Total by Category