Conditional Format Background, bad results on Drill Down

[cassidy]

Hello,

I have a table that I'm trying to "heat map" with background conditional formatting.  The Value is a $ Sales shown as % to Row Total.  The Results are as expected until I Drill Down.

Here it is, looks good while on I'm on the first set of Rows:

If I drill down to the second set of Rows, I can't figure out what it's doing.  Based on my knowledge of the data, where it's highlighting makes sense in the context of the entire table, but not the row. 

Marked a few areas in Blue where there should be an obvious background fill.

Here are my settings:

[themistoklis]

@cassidy 

what is the formula that you use in field 'Demand Sls $ TY' in the conditional formatting ?

[cassidy]

@themistoklis  I'm connected live to a large dataset, that measure in the dataset is SUM of a single Sales column.

[ebeery · Accepted solution]

@cassidy actually on second thought, I think I see what's happening.  The conditional formatting is not being applied to the %, it's being applied to the actual $ value.  So my guess is those areas you've marked in blue which don't have formatting are the lowest $ value rows.

[ebeery]

@cassidy just as a first troubleshooting step, what happens when you change the "Minimum" and "Maximum" settings from "Lowest value" and "Highest Value" to hard-coded values of 0 and 1, respectively?

[cassidy]

@ebeery  It colors the entire thing dark pink...which I'm realizing it's because these are not actual perctanges, PowerBI is just displaying it as a % to Row Total.  It's formatting based on the underlying Sales $ which are far greater than 1.

Can we not conditional format individual rows?   I think that's what's happening, I'm just getting lucky on the first screenshot that it appears to be based on rows, but it's really based on the Total Sales $.

[ebeery]

@cassidy I agree, I think it's just formatting based on the total sales $, not the %.

One option (which I'm thinking might be your best bet), would be to create a measure that actually calculates the % and use that in the visual and also in the conditional formatting rules, instead of relying on the "show values as" functionality.

[cassidy]

I think i'm still going to run into the inablity to conditional format each row.  Whether it's $ or %, I believe it's going to look at the whole table and then format, whereas I wan't each row to be indepently formatted.  It would be like defining each row as a format range in Excel, very tedious...not surprised BI is not allowing it.

[ebeery]

@cassidy ahh I see what you're saying.  The measure would be able to calculate the % of row total, but to your point, the formatting would be applied for the entire matrix, not for each row separately.