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 some rows.


The value 80.65 % should be highlighted in green whereas 19.35 % should be highlighted in red, but they are being highlighted opposite. May be I am doing something wrong?

Any help would be highly appreciated.

 

Regards,
Faisal Mehboob

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

3 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    3 years ago

    HI MFaisalMehboob 

     

    Can you share a sample of your data with Measure codes?
    Not sure, but Ithink you need to update your measure to compare numbers in each month, because I think in that row there is bigger capacity and lower Booked in June so those are correct but in July that measure compare the numbers with June as well.

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

    Appreciate your Kudos!! 

    LinkedIn | Twitter | Blog | YouTube 

  • 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