Conditional formatting in function to subtotal

[Syndicate_Admin]

Good afternoon

I am working with a matrix that returns sales progressions from the previous year (EJ: current month / same month previous year).

I need to create a conditional format that highlights the values that are major/less than the subtotal. Attached image

In this case I obtain that the average progression of these 4 items for the month of January was 44.84%. I need to generate a dax function that allows me to conditionally format (green background color) to the values above 44.84%, in this case highlight from green to: Butcher. Highlight with red background the values below the subtotal: Warehouse / bazaar / cold cuts).

The function I use for progression is:

Thank you!

[Anonymous]

Hi @Syndicate_Admin,

AFAIK, you can use dax expression to find out the current row content level, but the conditional color format seems not works on the total level.

Clever Hierarchy Handling in DAX - SQLBI

Perhaps you can consider submitting an idea for this requirement.

Regards,
Xiaoin Sheng

[lbendlin]

Remember that in a matrix visual your values are calculated four times. For the individual cells, for the row total, the column total, and the grand total.

So in your case you need a measure that only calculates the column total, ignoring the DescripcionRubro field.

something like 

CALCULATE([Ventas Totales],SAMEPERIODLASTYEAR(Calendario[Date]),REMOVEFILTERS(table, table[DescripcionRubro]))

That will be your column total that you can compare your current value to and then from that you can drive the conditional formatting.

Please provide sanitized sample data in usable format (not as a picture - inserting it into a table would be good) if you like more assistance.