Conditional Formatting Not Working Properly with Measures

[MFaisalMehboob]

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

[MFaisalMehboob]

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 · Accepted solution]

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

[VahidDM]

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