Create custom conditional formatting measure from column subtotals

Hi andre2x 

Thanks for the clarification, I’ve adjusted the logic and it’s now doing exactly what you described:

• If an Account-level cell has no Avg Revenue, it pulls in the parent Sales Grouping subtotal.
• Only those fallback values are color-coded (using red/yellow/green).
• Actual values remain clean and unformatted.

Use bellow measure in Matrix table.

Avg Revenue Display or Fallback = 
VAR CurrentAvg = [Avg Revenue]
VAR IsBlankRevenue = ISBLANK(CurrentAvg) || CurrentAvg = 0
VAR SelectedSalesGroup = SELECTEDVALUE('AvgRevenueMatrix'[Sales Grouping])
VAR SelectedServiceGroup = SELECTEDVALUE('AvgRevenueMatrix'[Service Group])
VAR FallbackSubtotal = CALCULATE([Avg Revenue],
REMOVEFILTERS('AvgRevenueMatrix'[Account]),'AvgRevenueMatrix'[Sales Grouping] = SelectedSalesGroup, 'AvgRevenueMatrix'[Service Group] = SelectedServiceGroup)
RETURN
IF(IsBlankRevenue, FallbackSubtotal, CurrentAvg)

After dragging Avg Revenue Display or Fallback measure in Matrix visual, use Avg Revenue Color Category in conditional formatting.

Avg Revenue Color Category = 
VAR CurrentAvg = [Avg Revenue]
VAR IsBlankRevenue = ISBLANK(CurrentAvg) || CurrentAvg = 0
VAR SelectedSalesGroup = SELECTEDVALUE('YourTable'[Sales Grouping])
VAR SelectedServiceGroup = SELECTEDVALUE('YourTable'[Service Group])
VAR FallbackSubtotal = CALCULATE([Avg Revenue], REMOVEFILTERS('YourTable'[Account]),
'YourTable'[Sales Grouping] = SelectedSalesGroup,'YourTable'[Service Group] = SelectedServiceGroup)
RETURN
IF(IsBlankRevenue, SWITCH(TRUE(), FallbackSubtotal < 5000, "Red", FallbackSubtotal < 15000, "Yellow","Green"),BLANK()  // Don't format if actual value exists)

Attached screenshot shows the result: blank cells are now visually meaningful without overriding valid data.

Let me know if you’d like to extend this logic to even higher levels like Industry or dynamic color thresholds.

________________________________________________________________________________________________________________________

If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.

Reagrds,
Akhil.