Forum Discussion

bishnu's avatar
bishnu
Helper II
6 years ago
Solved

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   Below is the % of total points and it compares with table a...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    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:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto

     

v-gizhi-msft's avatar
v-gizhi-msft
Community Support
6 years ago

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:

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto