Dynamic Conditional Formatting for Values and Totals in a Power BI Matrix

Because of the April 2020 update to Power BI Desktop, you can now conditionally format totals in the table and matrix visuals. Taking this one step further, this method will show you how to allow end-users to dynamically switch between showing conditional formatting for values, totals, values AND totals, or not applying any conditional formatting.

Watch the above video for a full explanation of how to set this up. The code for the single measure used in the trick is below:

Conditional Formatting Measure = 
VAR AvgRevenue = 
IF(
   ISINSCOPE('Sales Orders'[OrderDate].[Year]),
   AVERAGEX(ALLSELECTED('Sales Orders'[OrderDate].[Year]),[Total Revenue]),
   AVERAGEX(ALLSELECTED(Categories[Customer Category]),[Total Revenue])
)
RETURN
SWITCH(
   TRUE(),
   MAX('Conditional Formatting'[Selection]) = "Values" && ISINSCOPE('Sales Orders'[OrderDate].[Year]) && [Total Revenue] >= AvgRevenue, "#228B22",
   MAX('Conditional Formatting'[Selection]) = "Values" && ISINSCOPE('Sales Orders'[OrderDate].[Year]) && [Total Revenue] < AvgRevenue, "#ff0000",
   MAX('Conditional Formatting'[Selection]) = "Totals" && NOT(ISINSCOPE('Sales Orders'[OrderDate].[Year])) && [Total Revenue] >= AvgRevenue, "#228B22",
   MAX('Conditional Formatting'[Selection]) = "Totals" && NOT(ISINSCOPE('Sales Orders'[OrderDate].[Year])) && [Total Revenue] < AvgRevenue, "#ff0000",
   MAX('Conditional Formatting'[Selection]) = "Values + Totals" && [Total Revenue] >= AvgRevenue, "#228B22",
   MAX('Conditional Formatting'[Selection]) = "Values + Totals" && [Total Revenue] < AvgRevenue, "#ff0000",
   "#000000"
)

I hope you enjoyed learning this advanced trick! If you would like to access the data behind this report, make sure to check out my Training courses here. All memberships grant you access to a live SQL Server database to practice Power BI!