Forum Discussion

MFaisalMehboob's avatar
MFaisalMehboob
Frequent Visitor
3 years ago
Solved

Conditional Formatting Not Working Properly with Measures

Hi Team,   I am having a trouble with conditional formatting in Power BI Matrix visual. I have created the following conditional formatting on one of my measure and it is working in opposite for so...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    3 years ago

    Hi, MFaisalMehboob 

    Note that conditional formatting is based on an entire column of fields, not a single row of data.

    In the field "capacity",80.65% is a relatively low value, so it is  highlighted in red, and 98.39% is a relatively high value, so it is highlighted in green.

    In the field "booked",  1.61% is a relatively low value, so it is  highlighted in red, and 19.35% s a relatively high value, so it is highlighted in green.

     

    For your needs, I suggest creating conditional formatting based on custom measure fields

    Please add new measure like:

    Conditional_booked =
IF (
    [Booked] <> "N/A",
    IF (
        [Booked] < [Capacity],
        "Red",
        IF ( [Booked] > [Capacity], "Green", IF ( [Booked] = [Capacity], "Yellow" ) )
    )
)

    Conditional_capacity =
IF (
    [Capacity] <> "N/A",
    IF (
        [Booked] < [Capacity],
        "Green",
        IF ( [Booked] > [Capacity], "Red", IF ( [Booked] = [Capacity], "Yellow" ) )
    )
)

    Then apply them to the background conditions of their respective columns.

     

    Please checked my attached pbix for more details.

     

    Best Regards,
    Community Support Team _ Eason

MFaisalMehboob's avatar
MFaisalMehboob
Frequent Visitor
3 years ago

Hi VahidDM,

Let me see if I can she some sample date, but here is the code for my measures.

Booked =
IF(DIVIDE(
    IF([Reserved] = BLANK(), 0 , [Reserved]),
    IF([Available] = "N/A", 0, [Available])
) = BLANK(), "N/A" , DIVIDE(
    IF([Reserved] = BLANK(), 0 , [Reserved]),
    IF([Available] = "N/A", 0, [Available])
))

 

Capacity = IF((1 - IF([Booked] = "N/A", 1 , [Booked] )) = BLANK(), "N/A", (1 - IF([Booked] = "N/A", 1 , [Booked] )))
  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    3 years ago

    Hi, MFaisalMehboob 

    Note that conditional formatting is based on an entire column of fields, not a single row of data.

    In the field "capacity",80.65% is a relatively low value, so it is  highlighted in red, and 98.39% is a relatively high value, so it is highlighted in green.

    In the field "booked",  1.61% is a relatively low value, so it is  highlighted in red, and 19.35% s a relatively high value, so it is highlighted in green.

     

    For your needs, I suggest creating conditional formatting based on custom measure fields

    Please add new measure like:

    Conditional_booked =
IF (
    [Booked] <> "N/A",
    IF (
        [Booked] < [Capacity],
        "Red",
        IF ( [Booked] > [Capacity], "Green", IF ( [Booked] = [Capacity], "Yellow" ) )
    )
)

    Conditional_capacity =
IF (
    [Capacity] <> "N/A",
    IF (
        [Booked] < [Capacity],
        "Green",
        IF ( [Booked] > [Capacity], "Red", IF ( [Booked] = [Capacity], "Yellow" ) )
    )
)

    Then apply them to the background conditions of their respective columns.

     

    Please checked my attached pbix for more details.

     

    Best Regards,
    Community Support Team _ Eason