Need to compare values in same column but different rows

Hi Anonymous ,

According to your description, here's my solution.

1. Create a calculated column.

Result =
IF (
    'Table'[Hour]
        = MINX (
            FILTER (
                'Table',
                'Table'[Specification] = EARLIER ( 'Table'[Specification] )
                    && HOUR ( 'Table'[Hour] ) = HOUR ( EARLIER ( 'Table'[Hour] ) )
            ),
            'Table'[Hour]
        ),
    IF (
        'Table'[Green] = 1,
        "Green",
        IF (
            'Table'[Yellow] = 1
                && COUNTROWS (
                    FILTER (
                        'Table',
                        'Table'[Specification] = EARLIER ( 'Table'[Specification] )
                            && DATEDIFF ( EARLIER ( 'Table'[Hour] ), 'Table'[Hour], MINUTE ) <= 20
                            && DATEDIFF ( EARLIER ( 'Table'[Hour] ), 'Table'[Hour], MINUTE ) > 0
                            && 'Table'[Green] = 1
                    )
                ) = 0,
            "Yellow",
            IF (
                'Table'[Red] = 1
                    && COUNTROWS (
                        FILTER (
                            'Table',
                            'Table'[Specification] = EARLIER ( 'Table'[Specification] )
                                && DATEDIFF ( EARLIER ( 'Table'[Hour] ), 'Table'[Hour], MINUTE ) <= 20
                                && DATEDIFF ( EARLIER ( 'Table'[Hour] ), 'Table'[Hour], MINUTE ) > 0
                                && 'Table'[Green] = 1
                        )
                    ) = 0,
                "Red",
                "Green"
            )
        )
    )
)

Result:

2.If you put the columns in the visual and select count, it will get your snapshot result.

Instead, create three measures.

Hours in Green =
CALCULATE ( COUNT ( 'Table'[Result] ), 'Table'[Result] = "Green" )

Hours in Yellow =
CALCULATE ( COUNT ( 'Table'[Result] ), 'Table'[Result] = "Yellow" ) + 0

Hours in Red =
CALCULATE ( COUNT ( 'Table'[Result] ), 'Table'[Result] = "Red" ) + 0

 Then put the Result column and the measures in a visual, get the correct result.

3. Create a color table.

Create two measures.

Total =
SWITCH (
    MAX ( 'Color'[Color] ),
    "Green", 'Table'[Hours in Green],
    "Yellow", 'Table'[Hours in Yellow],
    "Red", 'Table'[Hours in Red]
)

% of Total =
DIVIDE ( 'Color'[Total], COUNT ( 'Table'[Result] ) )

Get the correct result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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