Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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
Community Champion
Community Champion

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?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.