Forum Discussion
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.
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.
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.
