Conditional Formatting of Font Color - Inconsistent Application in Drill Up & Down

[third_hicana]

HI Everyone,

Asking for your help. 

I have a matrix with multiple columns in the 'Columns' field (ie. budget type and money type)
Then I put a conditional formatting for font of color of values (£), red for positives and black for zeros or negatives under 'Variance' budget type. It works well when it is drilled down by 'BOP ID & Title' level.

However, if the matrix is drilled up at 'Lead Business Area' level, the conditional formatting of font color doesn't work. The negatives are also in color red as you can see below.

I am using a calculated column for the conditional formatting of font color below

Font Color =

SWITCH(

    TRUE(),

        consolidated[budget_type] = "Variance" && consolidated[money_type] = "CAPEX"  && [Round Consolidated Value] > 0.00, "Red",

        consolidated[budget_type] = "Variance" && consolidated[money_type] = "OPEX"  && [Round Consolidated Value] > 0.00, "Red",

        consolidated[budget_type] = "Variance" && consolidated[money_type] = "TOTAL"  && [Round Consolidated Value] > 0.00, "Red",
"Black"

         )


How can I apply the conditional formatting of font color when its is drilled up the same as when it is drilled down at ''BOP ID & Title' level.

Thank you in advance

-Third

[third_hicana · Accepted solution]

Here's the sample data. I wasn't able to upload the sample pbix file. I can't also share a one Drive link due to security reasons. Do you know how can I upload it here?

I also tried to replicate the matrix visual and it worked. But I am not sure why the conditional formatting does not work given I used the same DAX for the calculate column.

[Anonymous · Accepted solution]

Hi @third_hicana ,

Have you solved the problem?

Please try using a measure instead of a calculated column if the problem persists.

Sample:

Measure = SWITCH(TRUE(),MAX('consolidated'[budget type])="Variance" && SUM(consolidated[Value])>0,"Red","Black")

Best Regards,

Wearsky

[third_hicana · Accepted solution]

Here's the sample data. I wasn't able to upload the sample pbix file. I can't also share a one Drive link due to security reasons. Do you know how can I upload it here?

I also tried to replicate the matrix visual and it worked. But I am not sure why the conditional formatting does not work given I used the same DAX for the calculate column.

[suparnababu8]

Hi @third_hicana 

Try with this measure.

FontColor = 
SWITCH(TRUE(),
        MAX(consolidated[budget_type]) = "Variance" && MAX(consolidated[money_type]) = "CAPEX" && [Round Consolidated Value] > 0, "Red",
        MAX(consolidated[budget_type]) = "Variance" && MAX(consolidated[money_type]) = "OPEX" && [Round Consolidated Value] > 0, "Red",
        MAX(consolidated[budget_type]) = "Variance" && MAX(consolidated[money_type]) = "TOTAL" && [Round Consolidated Value] > 0, "Red",
    "Black")

Thanks!

[Ritaf1983]

Hi @third_hicana 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly