Forum Discussion

kivanct's avatar
kivanct
Icon for Helper I rankHelper I
3 years ago
Solved

Applying Color to the Closest Value

Hi everyone, I would like to share my issue. I would be grateful if you could help me to solve it.  These are the production lines in the first column like 1924-1925 etc.  My aim is "7". ...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi kivanct ,

     

    Please check this modified measure.

    Closet Value =
VAR _1 =
    SUM ( 'Table'[14 Saat] )
VAR _2 =
    SUM ( 'Table'[17.25 Saat] )
VAR _3 =
    SUM ( 'Table'[20.5 Saat] )
VAR _A1 =
    ABS ( _1 - 7 )
VAR _A2 =
    ABS ( _2 - 7 )
VAR _A3 =
    ABS ( _3 - 7 )
RETURN
    IF (
        _1 > 7
            && _2 > 7
            && _3 < 7,
        _3,
        IF (
            _1 > 7
                && _2 < 7
                && _3 > 7,
            _2,
            IF (
                _1 < 7
                    && _2 > 7
                    && _3 > 7,
                _1,
                IF (
                    _1 < 7
                        && _2 < 7
                        && _3 > 7,
                    IF ( _A1 >= _A2, _2, _1 ),
                    IF (
                        _1 < 7
                            && _2 > 7
                            && _3 < 7,
                        IF ( _A1 >= _A3, _3, _1 ),
                        IF (
                            _1 > 7
                                && _2 < 7
                                && _3 < 7,
                            IF ( _A2 >= _A3, _3, _2 ),
                            IF ( _A1 >= _A2, IF ( _A2 >= _A3, _3, _2 ), _1 )
                        )
                    )
                )
            )
        )
    )

     

    Best Regards,

    Stephen Tao

     

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi kivanct ,

 

Here's my solution.

1.Create a measure to return the colest values.

Closet Value = 
var _1=SUM('Table'[14 Saat])
var _2=SUM('Table'[17.25 Saat])
var _3=SUM('Table'[20.5 Saat])
var _A1=ABS(SUM('Table'[14 Saat])-7)
var _A2=ABS(SUM('Table'[17.25 Saat])-7)
var _A3=ABS(SUM('Table'[20.5 Saat])-7)
return IF(_A1>=_A2,IF(_A2>=_A3,_3,_2),_1)

 

2.Create three measures to return color results for different columns.

14Color = IF(SUM('Table'[14 Saat])=[Closet Value],"Green")
17.25Color = IF(SUM('Table'[17.25 Saat])=[Closet Value],"Green")
20.5Color = IF(SUM('Table'[20.5 Saat])=[Closet Value],"Green")

 

3.Use 'Conditional formatting' for each of three columns.

 

 

Best Regards,

Stephen Tao

 

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

 

 

  • kivanct's avatar
    kivanct
    Icon for Helper I rankHelper I
    3 years ago

    Thanks a lot. It worked ! But I just need a small adjustment.

    For example; 

    In row 1929, closest value shown as 7.39 but it should take "6" because 7,39 is greater than 7. 
    And 7.39 colored by green but 6 should be colored. 

    How can i adjust that ?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi kivanct ,

       

      Please check this modified measure.

      Closet Value =
VAR _1 =
    SUM ( 'Table'[14 Saat] )
VAR _2 =
    SUM ( 'Table'[17.25 Saat] )
VAR _3 =
    SUM ( 'Table'[20.5 Saat] )
VAR _A1 =
    ABS ( _1 - 7 )
VAR _A2 =
    ABS ( _2 - 7 )
VAR _A3 =
    ABS ( _3 - 7 )
RETURN
    IF (
        _1 > 7
            && _2 > 7
            && _3 < 7,
        _3,
        IF (
            _1 > 7
                && _2 < 7
                && _3 > 7,
            _2,
            IF (
                _1 < 7
                    && _2 > 7
                    && _3 > 7,
                _1,
                IF (
                    _1 < 7
                        && _2 < 7
                        && _3 > 7,
                    IF ( _A1 >= _A2, _2, _1 ),
                    IF (
                        _1 < 7
                            && _2 > 7
                            && _3 < 7,
                        IF ( _A1 >= _A3, _3, _1 ),
                        IF (
                            _1 > 7
                                && _2 < 7
                                && _3 < 7,
                            IF ( _A2 >= _A3, _3, _2 ),
                            IF ( _A1 >= _A2, IF ( _A2 >= _A3, _3, _2 ), _1 )
                        )
                    )
                )
            )
        )
    )

       

      Best Regards,

      Stephen Tao

       

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

      • kivanct's avatar
        kivanct
        Icon for Helper I rankHelper I
        3 years ago

        Worked ! Thanks a lot for your help.