Power BI Percentage of Total by Category [With Real Examples]

In this Power BI tutorial, we will learn how to display the  Power BI percent of total by category with examples.

And also, we will learn how we can display the percentage total by category with filters in Power BI. Furthermore, we will cover the below headings:

• Power BI percentage of total by category
• Power BI percentage of total by category with filter
• Power BI percentage of total by a category bar chart
• Power BI percentage of grand total by category
• Power BI percentage of total by category matrix
• Power BI percentage of total by group
• Power BI legend percentage of total by category
• Power BI percentage of column total by category
• Power Query percentage of total by category

Power BI percentage of total by category

Let us see how we can display the percent of the total value by category in the Power Bi report,

In this example, we will use the financials data table, to display the COGS percentage of total value based on the Product category 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.
• Use the below-mentioned formula to find the COGS percentage of total value based on the Product category.

COGS Percentage = 
Var Sumvalue = SUM(financials[COGS])
Var Category = CALCULATE ( SUM(financials[COGS]) , REMOVEFILTERS(financials[Product category]))
return
Sumvalue/Category

Where,

1. COGS Percentage = Measure Name
2. financials = Table Name
3. COGS, Product category = Column Names

Select the Power BI table visual from the visualization and drag-drop the created COGS Percentage measure value and product category field from the field pane to display the COGS percentage of total value based on the Product category as below:

• Change the Measure data type to a percentage by selecting Measure Tools -> Percentage as highlighted below:
• In the below screenshot, we can see that the table visually displays the COGS percentage value based on the Product category in the Power BI report.

This is how to display the percent of the total value by category in Power Bi.

Power BI percentage of total by category with filter

Here we will see how to calculate the percentage of the total profit value based on segment and filter by segment in Power BI.

In this example, we will calculate the percentage of the total profit value based on segment using Power Bi Measure and then we will filter the value using the Filter Pane.

• Load the data using the get data option into the Power Bi desktop, Now we will create a measure to calculate the profit percentage of the value of the segment.
• Click on the New measure from the ribbon. Then apply the below measure :

Segment Profit = 
VAR TotalProfit =
    SUM(financials[Profit])
VAR SegmentVariable =
    CALCULATE([Total Profit],ALL(financials[Segment]))
    RETURN
    DIVIDE ( TotalProfit, SegmentVariable )

Where,

1. Segment Profit = Measure Name
2. TotalProfit, Segment = Variable Names
3. financials = Table Name
4. Profit and Segment = Column Names

• Select the table visual from the visualization and drag-drop the created Segment Profit measure value and segment field from the field pane to display the segment profit percentage value based on the segment category as below:
• Selecting Measure tool -> Percentage to change the measure data type as a percentage.
• In the below screenshot, we can see that the table visually displays the segment profit percentage value based on the segment category.

• Expand the filter pane, and add the segment data fields into the data section.
• The screenshot below displays the segment based on the filtered value, you can see that I have filtered the segment category as Small Business.

This is how to calculate the percentage of the total profit value based on segment and filter by segment in Power BI.

Read Power BI Percent of Total

Power BI percentage of total by a category bar chart

Let us see how we can display the percentage of the total by category in a bar chart in Power Bi.

In this example, we will use the Stacked bar chart to display the discount percentage of the total based on the Product category.

• Load the data into the Power Bi desktop, Now we will create a measure to calculate the discount percentage of the value of the product category.
• Click on the New measure from the ribbon. Then apply the below measure :

Discount Percentage = 
Var discountsum = SUM(financials[Discounts])
Var Category = CALCULATE ( SUM(financials[Discounts]) , REMOVEFILTERS(financials[Product category]))
return
discountsum/Category

Where,

1. Discount Percentage = Measure Name
2. financials = Table Name
3. Discounts, Product category = Column Names

• Select the stacked bar chart visual from the visualization and drag-drop the created discount Percentage measure value in the x-axis field.
• In the Y-axis field, drag and drop the Product category field from the field pane as below:

In the below screenshot, we can see that the stacked bar chart displays the percentage based on the product category in the Power Bi report.

This is how to display the percentage of the total by category in a bar chart in Power Bi.

Power BI percentage of grand total by category

Let us see how to display the percent of the total value by category in the Power Bi report,

In this example, we will display the sales value based on the sales percentage of grand total by Product category in Power Bi.

• Log in to the Power Bi desktop and load data into it. Once the data has been loaded click on the new measure under the modeling tab.
• Use the below-mentioned formula to find the Sales percentage of total value based on the Product category.

Where,

Sales % = 
Var SalesTotal = SUM(financials[ Sales])
Var ProductCatgory = CALCULATE ( SUM(financials[ Sales]) , REMOVEFILTERS(financials[Product category]))
return
SalesTotal/ProductCatgory

Where,

1. Sales % = Measure Name
2. SalesTotal , ProductCatgory = Variable Names
3. financials = Table Name
4. Sales = Column Name

Select the table visual from the visualization and drag-drop the created measure value and product category field from the field pane to display the total sales value as below:

• Now expand the sales % field and select Show value as ->Percent of grant total as highlighted below:
• The screenshot below displays the sales percentage value based on the product category.

This is how to display the percent of the total sales value by product category in the Power Bi report.

Also, check How to Concatenate Text and Number in Power BI

Power BI percentage of total by category matrix

Let us see how we can display the percentage of total sales by category in a matrix visual in Power BI.

In this example, we will display the sales value based on the sales percentage of total by Product category in Power Bi.

• Open 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.
• Use the below formula to find the total sales value by category.

Sales % = 
Var SalesTotal = SUM(financials[ Sales])
Var ProductCatgory = CALCULATE ( SUM(financials[ Sales]) , REMOVEFILTERS(financials[Product category]))
return
SalesTotal/ProductCatgory

Where,

1. Sales % = Measure Name
2. SalesTotal , ProductCatgory = Variable Names
3. financials = Table Name
4. Sales = Column Name

• Now select the Matrix visual from the visualization and drag-drop the ProductCategory field in the row section.
• In the values section, drag and drop the measure values as shown below:

• Change the Measure data type to a percentage by selecting Measure Tools -> Percentage as highlighted below:
• In the below screenshot, we can see that the matrix visually displays the Sales percentage value based on the Product category in the Power BI report.

This is how to display the percentage of total sales by category in a matrix visual in Power BI.

Power BI percentage of total by group

Here we will see how to calculate the percentage of sales based on the group using the measure in power bi.

In this example, we will display the sales value by grouping country and product categories in the Power Bi report.

• Load the data using the get data option from the ribbon in the power bi desktop, and then write the below measure to group the values by country:

Country Group = DIVIDE(SUM(financials[ Sales]),CALCULATE(SUM(financials[ Sales]),ALLSELECTED(financials[Country])  ) )

Where,

1. Country Group = Measure Name
2. financials = Table Name
3. Sales, Country = Column Names

In the same way, use the below measure value to group the values by product category:

Product Group = DIVIDE(SUM(financials[ Sales]),CALCULATE(SUM(financials[ Sales]),ALLSELECTED(financials[Product category])  ) )

Where,

1. Product Group = Measure Name
2. financials = Table Name
3. Sales, Product Group = Column Names

To find the Group percentage value, use the below formula:

Group% = if([Product Group]=1,financials[Country Group],financials[Product Group])

Where,

1. Group% = New Measure Name
2. Product Group, Country Group = Existing Measure Name

• Select the Matrix visual from the visualization and drag-drop the Country and Product Category fields in the row section.
• In the values section, drag and drop the Sales field and created Group % measure value as shown below:

• Change the Measure data type to a percentage by selecting Measure Tools -> Percentage as highlighted below:
• In the below screenshot, we can see that the matrix visually displays the Sales percentage value based on the Product category by grouping countries in the Power BI report.

This is how to calculate the percentage of sales based on the group using the measure in power bi.

Also, check out, How to Remove Leading Zeros in Power BI

Power BI legend percentage of total by category

Here we will see how to display the percentage of the total by category with a legend in Power BI,

In this example, we will use the Stacked bar chart to display the discount percentage of the total based on the Product category, and we will add the country field as a legend.

• Load the data into the Power Bi desktop, Then create a measure to calculate the discount percentage of the value of the product category.
• Click on the New measure from the ribbon. Then apply the below measure :

Discount Percentage = 
Var discountsum = SUM(financials[Discounts])
Var Category = CALCULATE ( SUM(financials[Discounts]) , REMOVEFILTERS(financials[Product category]))
return
discountsum/Category

Where,

1. Discount Percentage = Measure Name
2. financials = Table Name
3. Discounts, Product category = Column Names

• Select the stacked bar chart visual from the visualization and drag-drop the created discount Percentage measure value in the x-axis field.
• In the Y-axis field, drag and drop the Product category field from the field pane as below:

• Now in the legend field section drag and drop the country field from the field pane. Expand the Discount Percentage field and select Show value as ->Percent of grant total as highlighted below:

• In the below screenshot, we can see that the stacked bar chart displays the percentage of the total based on the product category with Country legend in the Power Bi.

This is how to display the percentage of the total by category with a legend in Power BI.

Power BI percentage of column total by category

Let us see how we can display the Percentage of column total by category in Power Bi,

In this example, we will add a new column to calculate the total percentage value for the sales column based on the product category.

Load data into the Power bi desktop, to find the percentage total, click on the new column under the modeling tab and use the below formula:

Sales Percentage = financials[ Sales]/CALCULATE(SUM(financials[ Sales]),ALLSELECTED())*100

Where,

1. Sales Percentage = New Column name
2. financials = Table Name
3. Sales = Existing Column Name

• Select the table visual from the visualization and drag-drop the created Sales column value Product category, and sales fields from the field pane to display the sales percentage value based on the product category as below:

• Now expand the sales percentage field and select Show value as ->Percent of grant total as highlighted below:
• The screenshot below displays the sales percentage value based on the product category.

This is how to display the Percentage of column total by category in Power Bi.

Read Power BI Distinct Count with filter

Power Query percentage of total by category

Here we will see how we can display the Percentage of the total by category using the Power Query editor in Power Bi.

In this example, we will calculate the sales percentage of the total by category value using the power query editor in Power BI.

• Load the financial data table into the Power Bi desktop, Under the Home, tab selects the transform data option as highlighted below, it will automatically redirect to the Power Query editor.

• In the power query editor, Click on Transform tab -> Group by. click on Group By icon from the ribbon.

• Then the Group By window will appear, Click on the Advanced options, by default it displays the Product category.
• Next, enter the new column name, select Operation as Sum, and select the Column as Sales. In the same way, click on the Add aggregation enter the new column name and choose the operation as All rows as highlighted below:
• Then click on the OK button.

In the below screenshot, you can see that it displays the grouped product categories with the total sales value.

= Table.Group(#"Renamed Columns6", {"Product category"}, {{"Total Sales", each List.Sum([#" Sales"]), type nullable number}, {"Details", each _, type table [Segment=nullable text, Country=nullable text, Product category=nullable text, Discount Band=nullable text, Units Sold=nullable number, Manufacturing Price=nullable number, Sale Price=nullable number, Gross Sales=nullable number, Discounts=nullable number, #" Sales"=nullable number, COGS=nullable number, Profit=nullable number, Date=nullable date, Month Name=text, Year=nullable text, Month and Year=text]}})

• Now Expand the table option presented in the details and click on the Ok button as shown below:

The screenshot below expands the table data as expected, next we will find the percentage total value of sales.

= Table.ExpandTableColumn(#"Grouped Rows", "Details", {"Segment", "Country", "Discount Band", "Units Sold", "Manufacturing Price", "Sale Price", "Gross Sales", "Discounts", " Sales", "COGS", "Profit", "Date", "Month Name", "Year", "Month and Year"}, {"Segment", "Country", "Discount Band", "Units Sold", "Manufacturing Price", "Sale Price", "Gross Sales", "Discounts", " Sales", "COGS", "Profit", "Date", "Month Name", "Year", "Month and Year"})

To find the Percentage total value of sales, select Add column -> Custom Column as displayed below:

• In the custom column window, Enter the new column and write the below formula to find the percentage of total sales value, and click on the Ok button.

= [#" Sales"]/[Total Sales]

Where,
Sales & Total Sales = Existing Columns

In the below screenshot, you can see that the newly added custom column displays the percent value in Power Bi.

= Table.AddColumn(#"Expanded Details", "% of Sales", each [#" Sales"]/[Total Sales])

To show sales percent value change or transform data type as a percentage. For that under the home tab, select Data type -> Percentage.

= Table.TransformColumnTypes(#"Added Custom1",{{"% of Sales", Percentage.Type}})

• In the below screenshot, we can see the custom column displays the percentage of total by category in the percentage format.
• Click on the Close and Apply option to reflect the changes from the Power Query editor to the Power Bi desktop.

This is how to display the Percentage of the total by category using the Power Query editor in Power Bi.

In this Power BI tutorial, we have learned how to display the  Power BI percent of the total by category with examples, And also we learned how we can display the percentage total by category with filters in Power BI. And also we covered the below headings:

• Power BI percentage of total by category
• Power BI percentage of total by category with filter
• Power BI percentage of total by a category bar chart
• Power BI percentage of grand total by category
• Power BI percentage of total by category matrix
• Power BI percentage of total by group
• Power BI legend percentage of total by category
• Power BI percentage of column total by category
• Power Query percentage of total by category

You may like the following Power BI tutorials:

• How to Convert Number to Text in Power BI
• Power BI sort table by Date
• How to sort by multiple columns in Power BI