A cross table complex condition

Hi Lily36876 

The solution ryan_mayu  provided is excellent, I want to offer some more information for you to refer to, the sample data is the same as you provided.

You can create the following measure.

MEASURE =
VAR a =
    CALCULATE (
        MAX ( 'Monthly incentive amount'[Incentive] ),
        'Monthly incentive amount'[ShopNo]
            = MAX ( 'Sales personnel store counter'[ShopNo] ),
        'Monthly incentive amount'[YearMonth]
            = MAX ( 'Sales personnel store counter'[YearMonth] )
    )
VAR b =
    CALCULATE (
        COUNTROWS ( 'Sales personnel store counter' ),
        ALLSELECTED ( 'Sales personnel store counter' ),
        'Sales personnel store counter'[ShopNo]
            IN VALUES ( 'Sales personnel store counter'[ShopNo] ),
        'Sales personnel store counter'[YearMonth]
            IN VALUES ( 'Sales personnel store counter'[YearMonth] ),
        'Sales personnel store counter'[Achieve] = "YES"
    )
RETURN
    IF (
        MAX ( 'Sales personnel store counter'[Achieve] ) <> BLANK (),
        DIVIDE ( a, b )
    )

Output

Best Regards!

Yolo Zhu

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