Create custom conditional formatting measure from column subtotals

[andre2x]

Hi, I'm having trouble coming up with the right measure to apply conditional formatting on this matrix table. I understand the process of using a measure as the values to base a conditional formatting on, but I'm having trouble getting it to return the right results. I am using a matrix with 3 fields for the rows, Industry > Sales Grouping > Account. For the columns, I am using the Service Group field, and for the values Average Revenue. For each cell, I'd like to have the following logic:

• For every cell with a blank ($0) Avg Revenue, return the subtotal of that cell's next highest hierarchy level
• I will use these values to format Red-Yellow-Green based on their values from lowest to highest
• For example, Attest-Audit Service Group has a high Avg Revenue of $35,504 for Accounts in the $100-$, so all blank cells within that column should have a Green background
• Attest-Reviews only has an Avg Revenue of $4,000, so that blank cell should be Red, reflecting that as the lowest value.
  

I'm not sure if it's possible to extend this logic to higher hierarchy levels like Sales Grouping, but I would at least like it at the lowest level, Account. Let me know if more clarification is needed. Thanks!

[v-agajavelly]

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.

[v-agajavelly]

Hi @andre2x 

I’ve actually worked through this exact scenario and got a working solution that does what you’re looking for.
You wanted each blank ($0) cell in the matrix to:

• Inherit the subtotal from its immediate parent level (Sales Grouping) for the same Service Group column.
• Use that value for color-based conditional formatting (red-yellow-green) — so even blank cells get visual context.

To achieve this, I created a DAX measure that,

• Checks if the current [Avg Revenue] is blank or zero.
• If so, it grabs the subtotal at the Sales Grouping level for that same Service Group.
• It returns either the actual value or the fallback subtotal — which can then be used for formatting.

 
Avg Revenue Conditional Format = 
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, FallbackSubtotal, CurrentAvg)

(Just replace 'YourTable' with your table name and [Avg Revenue] with your existing measure.)

Now use this measure applying in conditional formatting.Even the blank cells are now shaded using their parent subtotal’s value, giving consistent, meaningful color context across the entire matrix.

Attaching snip for your refferance.

________________________________________________________________________________________________________________________

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

Reagrds,
Akhil.

[andre2x]

Thanks Akhil, I'm on the right track with this but I'm more looking to get the parent averages to display when the Account Avg Revenue is blank for a service group, then color code from there. So cells with Avg Revenue in them will not be color coded.