Power BI Conditional Formatting Based on Measure

In this Power BI tutorial, we will learn how to apply conditional formatting based on a Measure Percentage value in Power BI with various examples. In addition, we will also cover the below-mentioned topics:

  • Power BI conditional formatting based on a measure bar chart
  • Power BI conditional formatting based on the measure not working
  • Power BI conditional formatting based on two measures

Power BI conditional formatting based on measure Percentage

Let us see how we can display the percent of the total value by measure 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 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.
COGS Percentage = 
Var Sumvalue = SUM(financials[COGS])
Var Category = CALCULATE ( SUM(financials[COGS]) , REMOVEFILTERS(financials[Product]))
return
Sumvalue/Category

Where,

  1. COGS Percentage = Measure Name
  2. financials = Table Name
  3. COGSProduct = Column Names
  • Select the Power BI table visual from the visualization and drag-drop the created COGS Percentage measure value and product field from the field pane.
  • To display the COGS percentage of total value based on the Product select the created measure Measure Tools -> Percentage  as 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.
Power BI conditional formatting based on measure Percentage
Power BI conditional formatting based on measure Percentage
  • To apply conditional formatting, select the Format Visual under the Visualisation pane, and Expand Cell Elements under the Visual tab (highlighted in Red):
  • Enable the background color and click on the Fx icon as shown below:
Power BI conditional formatting based on measure Percentage example
Power BI conditional formatting based on measure Percentage example
  • Now, Select the Format style as Rules and choose the measure field value, Now add the rule that you want to apply. Select +New rule to add a new rule condition.
  • Add the condition to apply the conditional formatting for the created measure value.
  • Click on the Ok button to display the table visual based on the condition applied.
conditional formatting based on two measures in the Power BI
conditional formatting based on two measures in the Power BI

In the below screenshot, you can see that the displays the conditional formatting value for the created measure value is applied for the table visual.

Power BI conditional formatting based on measure value
Power BI conditional formatting based on the measure value

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

See also  Power BI Divide Two Columns + 8 Examples

Also Read: Power BI Conditional Formatting Based on Text

Power BI conditional formatting based on a measure bar chart

Here we will see how we can apply conditional formatting based on a measure bar chart in Power BI.

In this example, we will display the created measure COGS Percentage value in the stacked bar chart in the Power BI report.

  • Load source data into the Power Bi desktop. Selected the stacked bar chart visual drag and drop the Product field and the calculated COGS percentage measure value in the field section.
Power BI conditional formatting based on a measure bar chart
Power BI conditional formatting based on a measure bar chart
  • To apply conditional formatting for bars, select the Format Visual under the Visualisation pane, and Expand Bars -> Colors under the Visual tab (highlighted in Red):
  • Enable the background color and click on the Fx icon as shown below:
Power BI conditional formatting based on a measure bar chart example
Power BI conditional formatting based on a measure bar chart example
  • Now, Select the Format style as Rules and choose the column field value, Now add the rule that you want to apply. Select +New rule to add a new rule condition.
  • Add the condition to apply the conditional formatting for the created COGS Percentage measure value.
  • Click on the Ok button to display the table visual based on the condition applied.
Example of Power BI conditional formatting based on a measure bar chart
Example of Power BI conditional formatting based on a measure bar chart

In the below screenshot, you can see that the displays the conditional formatting value for the created measure value is applied to the stacked bar chart in Power Bi.

conditional formatting based on measure value in the Power BI
conditional formatting based on measure value in the Power BI

This is how to apply conditional formatting based on a measure bar chart in Power BI.

Power BI conditional formatting based on the measure not working

  • No, it is possible to apply conditional formatting based on the crated measure value presented in the field pane.
  • In the conditional formatting pop-up window, based on Format by rules we can select the measure value field and apply the conditional formatting.
  • In the following example, you can see how to work with conditional formatting based on the measures.
See also  Difference Between Calculated Column and Measure in Power BI

Power BI conditional formatting based on two measures

Let us see how we can apply conditional formatting based on two measures in the Power Bi report.

In the example, we will use the two measure values as COGS percentage value and Discount Percentage value in Power BI.

  • Once the data has been loaded to the Power BI desktop, click on the new measure under the modeling tab.
  • Use the below-mentioned formula to find the Discount percentage of the total value based on the Product.
Discount Percentage = 
Var discountsum = SUM(financials[Discounts])
Var Category = CALCULATE ( SUM(financials[Discounts]) , REMOVEFILTERS(financials[Product]))
return
discountsum/Category

Where,

  1. Discount Percentage = Measure Name
  2. financials = Table Name
  3. DiscountsProduct  = Column Names
  • Select the table visual, and drag-drop the created two measure values in it to display the values in percentage format select the created measure Measure Tools -> Percentage  as below:
  • In the below screenshot, we can see that the table visually displays the previously created COGS percentage value and discount percentage value based on the Product in the Power BI report.
Power BI conditional formatting based on two measures
Power BI conditional formatting based on two measures
  • To apply conditional formatting for table cells, select Format Visual under the Visualisation pane.
  • Expand Cell Elements and apply settings to the series called COGS Percentage measure value, enable the background color and click on the Fx icon as shown below:
Power BI conditional formatting based on two measures example
Power BI conditional formatting based on two measures example
  • Now, Select the Format style as Rules and choose the measure field value, Now add the rule that you want to apply. Select +New rule to add a new rule condition.
  • Add the condition to apply the conditional formatting for the created COGS Percentage measure value.
  • Click on the Ok button to display the table visual based on the condition applied.
conditional formatting based on two measures in the Power BI
conditional formatting based on two measures in the Power BI

In the same way, select the applied setting to series as discount percentage measure value and apply conditional formatting.

Example of conditional formatting based on two measures in Power BI
Example of conditional formatting based on two measures in Power BI
  • Now, Select the Format style as Rules and choose the measure field value, Now add the rule that you want to apply. Select +New rule to add a new rule condition.
  • Add the condition to apply the conditional formatting for the created Discount Percentage measure value.
  • Click on the Ok button to display the table visual based on the condition applied.
conditional formatting based on two measures in Power BI example
conditional formatting based on two measures in the Power BI example

In the below screenshot, you can see that the displays the conditional formatting value for the created two measure values is applied for the table visual.

Example of Power BI conditional formatting based on two measures
Example of Power BI conditional formatting based on two measures

This is how to apply conditional formatting based on two measures in the Power BI Report.

See also  How to create a report in Power BI Desktop? [Excel & SharePoint]

In this Power BI tutorial, we have learned how to apply conditional formatting based on a measure percentage value in Power BI with various examples. In addition, we also covered the below-mentioned topics:

  • Power BI conditional formatting based on a measure bar chart
  • Power BI conditional formatting based on the measure not working
  • Power BI conditional formatting based on two measures

You may also like the following Power BI tutorials:

>