Power BI Conditional Formatting based on Measure

Recently, I was working on a Power BI Report when a client asked me to apply conditional formatting based on the measures in the Power BI visual.

This Power BI tutorial shows how to work with Power BI conditional formatting based on measure with various examples.

Power BI Conditional Formatting based on Measure

In this example, we will create a measure, and then I’ll guide you through applying conditional formatting in Power BI.

Here, I have a SharePoint list (Financial Sample) that contains below columns with various data types:

ColumnsData Types
ProductSingle line of text
CountrySingle line of text
Units SoldNumber
Sale PriceCurrency
Gross SalesCurrency
ProfitCurrency
SalesCurrency
conditional formatting measure power bi

Follow the below steps to apply conditional formatting based on a measure in Power BI:

1. Open Power BI Desktop and load the data. Then, you see data in the Data pane.

conditional formatting on measure power bi

2. Under the Home tab, click the New measure.

how to do conditional formatting based on measure in power bi

3. In the formula bar, click the below expression. Then click the Commit button.

Profit Margin % = 
DIVIDE(
		SUM('Financial Sample'[Profit]),
		SUM('Financial Sample'[Gross Sales])
	)

Where:

  • Profit Margin % = Name of the measure
  • DIVIDE = DAX Function
  • SUM = DAX Function
  • Financial Sample = Name of the table
  • Profit = Name of the column
  • Gross Sales = Name of the column
conditional formatting based on another measure power bi

4. Click the Home tab -> Expand Visual gallery(black box) -> Click the Matrix visual.

conditional formatting measure in power bi

5. Then, using the +Add data option, add the Country into the Rows field, Product into the Columns field, and Profit Margin % into the Values field.

power bi conditional formatting based on measure value

6. Now, we apply conditional formatting. Select Matrix Visual -> Expand Format pane -> click Visual -> Expand Cell element. Then you find many options (i.e., Background color, Font color, Data bars, Icons, and Web URL). Using this option, you can apply conditional formatting.

Note:

If you don’t know how to apply conditional formatting on matrix visual. I have already write a full article Power BI Matrix Conditional Formatting.
power bi conditional formatting based on value

But our requirement is conditional formatting based on the measure. To do this, we need to create a measure.

7. Under the Home tab, click the New measure.

how to do conditional formatting based on measure in power bi

8. In the formula bar, click the below expression. Then click the Commit button.

conditional formatting = 
IF(
		[Profit Margin %] < 0.10,
		"Gray",
		IF(
			[Profit Margin %] < 0.20,
			"Yellow",
			IF(
				[Profit Margin %] < 0.30,
				"brown",
				IF(
					[Profit Margin %] < 0.40,
					"green",
					IF(
						[Profit Margin %] < 0.50,
						"blue",
						"purple"
					)
				)
			)
		)
	)

Where:

  • conditional formatting = Name of the new Measure
  • Profit Margin % = Name of the Measure
  • IF = DAX Function
  • Gray, Yellow, brown, green, blue, purple = Name of the Color
  • 0.10, 0.20, 0.30, 0.40, 0.50 = Percentage Value
power bi conditional formatting based on measure percentage

9. Select Matrix Visual -> Expand Format pane -> click Visual -> Expand Cell element -> On the Background color button -> click fx.

power bi conditional formatting text based on measure

10. Select Format style as Field value, and What field should we base this on? as conditional formatting, then click OK.

power bi conditional formatting based on field value

11. Then conditional formatting is applied successfully based on the measure in the Power BI matrix visual.

Conditional Formatting based on Measure in Power BI

Using the above way, you can apply conditional formatting based on the measures in Power BI visual.

Conclusion

I hope this Power BI tutorial provides valuable insights into applying conditional formatting based on measures.

In this tutorial, we covered applying conditional formatting based on measures in Power BI visual.

Also, you may like some more Power BI articles: