cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bishnu
Helper II
Helper II

Conditional formatting between 2 intervals

Hello

I am trying to formulate an condutional formating, so that if the data falls between the range, it highlights the cell

 

Intended resultIntended resultPowerbiPowerbi

Below is the % of total points and it compares with table above to highlight where the score falls. As 38 < 40 it will hightlight as >25%. Both are different tables and i tried to formulate the following formula with if and it dosent work

IF(calculate(sum('below'[Points%]))>='above'[% Score],if(calculate(sum('below'[Points%]))<='above'[% Score],←,""),"")
 
Regards
BB
1 ACCEPTED SOLUTION
v-gizhi-msft
Community Support
Community Support

Hi,

 

After some test, i found your logic about [Point(%)], as below column shows:

Points(%) = DIVIDE('Table'[Weighted(Part1)],DIVIDE('Table'[Max(Part1)],'Table'[Actual],0),0)

Then please try this column in Level table:

Part1 = 
IF (
    SUM ( 'Table'[Points(%)] ) > 'Level'[%SCORE],
    IF (
        'Level'[%SCORE]
            = CALCULATE (
                MAX ( 'Level'[%SCORE] ),
                FILTER ( 'Level', 'Level'[%SCORE] < SUM ( 'Table'[Points(%)] ) )
            ),
        "←"
    )
)

Try this measure:

Color = IF(MAX('Level'[Part1])<>BLANK(),"#FF0000")

Apply this measure to the [Part1], the result shows:

26.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

 

View solution in original post

3 REPLIES 3
v-gizhi-msft
Community Support
Community Support

Hi,

 

After some test, i found your logic about [Point(%)], as below column shows:

Points(%) = DIVIDE('Table'[Weighted(Part1)],DIVIDE('Table'[Max(Part1)],'Table'[Actual],0),0)

Then please try this column in Level table:

Part1 = 
IF (
    SUM ( 'Table'[Points(%)] ) > 'Level'[%SCORE],
    IF (
        'Level'[%SCORE]
            = CALCULATE (
                MAX ( 'Level'[%SCORE] ),
                FILTER ( 'Level', 'Level'[%SCORE] < SUM ( 'Table'[Points(%)] ) )
            ),
        "←"
    )
)

Try this measure:

Color = IF(MAX('Level'[Part1])<>BLANK(),"#FF0000")

Apply this measure to the [Part1], the result shows:

26.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

 

Greg_Deckler
Super User
Super User

I am not following this. Is this just a question about the total row? And only conditional formatting the total row? You know where to find the Conditional Formatting options, correct? Sorry, could you clarify?


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Hello @Greg_Deckler 

Apologies for not explaning better

The table below is based on the calculation. The final result is the total of %points(Approx 38%, highlighted in red).

Now i need to compare this score with the table above to see which value range it falls. 20%<38%<40%, so it will fall in the range of 20%. Both are 2 different tables. 

I tried to formulate with IF but it dosent work. I used "←" if condition is true, and "<blank>" if false

 

Regards

Bishnu

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors