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. ProfitCountry = 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:
Power BI Calculate Percentage of Row
Power BI Calculate Percentage of Row
  • 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.
Power BI Calculate Percentage of Row example
Power BI Calculate the Percentage of Row example

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. COGSProduct 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:
Percentage of parent Row total in Power BI
Percentage of parent Row total in Power BI
  • 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.
Example Percentage of parent Row total in Power BI
Example of Percentage of parent Row total in Power BI

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. SalesProduct 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:
Percentage of Row total in Power Bi
Percentage of Row total in Power Bi
  • 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.
Power BI DAX Calculate the Percentage of the Row total
Power BI DAX Calculate the Percentage of the Row total

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. UnitSoldAll ProductsResult = 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:
Power BI Calculate the Percentage of the subtotal
Power BI Calculate the Percentage of the subtotal
  • 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.
Power BI Calculate the Percentage of the Row total
Power BI Calculate the Percentage of the subtotal example

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)
Power BI calculates the percentage difference between two rows
Power BI calculates the percentage difference between two rows

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.

Example of Power BI calculates the percentage difference between two rows
An example of Power BI calculates the percentage difference between two rows

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:
Power BI calculates the percentage difference between two rows example
Power BI calculates the percentage difference between two rows example
  • 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.
Power BI calculates a percentage difference between two rows
Power BI calculates a percentage difference between two rows

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:

>