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!


Parker Stevens

Parker is the creator of the BI Elite YouTube channel, a community of over 20,000 students learning Power BI, DAX, and Power Query. He is a Microsoft Data Platform MVP for his work with Power BI and continues to provide elite Power Platform training courses to help data analysts, BI developers, and citizen dashboard creators reach their goals. Parker is committed to producing high-quality training content that is also extremely cost-effective, to ensure that the largest amount of users can benefit from the content. Training courses located at https://training.bielite.com/