Forum Discussion

third_hicana's avatar
third_hicana
Helper IV
1 year ago
Solved

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

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

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • third_hicana's avatar
    third_hicana
    1 year ago

     

    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's avatar
    Anonymous
    1 year ago

    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

4 Replies

  • suparnababu8's avatar
    suparnababu8
    Super User
    1 year ago

    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!

  • third_hicana's avatar
    third_hicana
    Helper IV
    1 year ago

     

    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's avatar
    Anonymous
    Not applicable
    1 year ago

    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