top 2 sales Based on Top 2 Target

Hi rajasekar_o ,

Depending on the information you have provided, you can follow these steps below:

1.Add an index column.

2.Add new measures.

RANK_Y =
VAR _Y =
    RANKX (
        FILTER ( ALL ( 'employee master' ), 'employee master'[t20] = "Y" ),
        CALCULATE ( MAX ( 'target data'[TARGET] ) ),
        ,
        ASC
    )
RETURN
    IF ( SELECTEDVALUE ( 'employee master'[t20] ) = BLANK (), BLANK (), _Y )

ASM:

ASM-T2 RANK = 
VAR _ASM =
    SELECTEDVALUE ( 'employee master'[ASM CODE] )
VAR _RESULT =
    RANKX (
        FILTER ( ALL ( 'employee master' ), 'employee master'[ASM CODE] = _ASM ),
        [RANK_Y],
        ,
        DESC
    )
RETURN
    IF ( _RESULT < 3, _RESULT, BLANK () )

ASM_ = 
VAR _ASM =
    SELECTEDVALUE ( 'employee master'[ASM CODE] )
VAR _RANK =
    RANKX (
        FILTER ( ALL ( 'employee master' ), 'employee master'[ASM CODE] = _ASM ),
        CALCULATE ( MAX ( 'employee master'[Index] ) ),
        ,
        DESC
    )
VAR _SUM =
    CALCULATE (
        SUM ( 'sales data'[sales] ),
        FILTER (
            ALL ( 'employee master' ),
            'employee master'[ASM CODE] = _ASM
                && [ASM-T2 RANK] <> BLANK ()
        )
    )
VAR _OUTPUT =
    IF ( _RANK = 1, _SUM, BLANK () )
RETURN
    _OUTPUT

ASM T2 = CALCULATE(SUMX('employee master',[ASM_]))

 

ASO:

ASO-T2 RANK = 
VAR _ASO =
    SELECTEDVALUE ( 'employee master'[ASO CODE] )
VAR _RESULT =
    RANKX (
        FILTER ( ALL ( 'employee master' ), 'employee master'[ASO CODE] = _ASO ),
        [RANK_Y],
        ,
        DESC
    )
RETURN
    IF ( _RESULT < 3, _RESULT, BLANK () )

ASO_ = 
VAR _ASO =
    SELECTEDVALUE ( 'employee master'[ASO CODE] )
VAR _RANK =
    RANKX (
        FILTER ( ALL ( 'employee master' ), 'employee master'[ASO CODE] = _ASO ),
        CALCULATE ( MAX ( 'employee master'[Index] ) ),
        ,
        DESC
    )
VAR _SUM =
    CALCULATE (
        SUM ( 'sales data'[sales] ),
        FILTER (
            ALL ( 'employee master' ),
            'employee master'[ASO CODE] = _ASO
                && [ASO-T2 RANK] <> BLANK ()
        )
    )
VAR _OUTPUT =
    IF ( _RANK = 1, _SUM, BLANK () )
RETURN
    _OUTPUT

ASO T2 = CALCULATE(SUMX('employee master',[ASO_]))

 

BM:

BM-T2 RANK = 
VAR _BM =
    SELECTEDVALUE ( 'employee master'[BM CODE] )
VAR _RESULT =
    RANKX (
        FILTER ( ALL ( 'employee master' ), 'employee master'[BM CODE] = _BM ),
        [RANK_Y],
        ,
        DESC
    )
RETURN
    IF ( _RESULT < 3, _RESULT, BLANK () )

BM_ = 
VAR _BM =
    SELECTEDVALUE ( 'employee master'[BM CODE] )
VAR _RANK =
    RANKX (
        FILTER ( ALL ( 'employee master' ), 'employee master'[BM CODE] = _BM ),
        CALCULATE ( MAX ( 'employee master'[Index] ) ),
        ,
        DESC
    )
VAR _SUM =
    CALCULATE (
        SUM ( 'sales data'[sales] ),
        FILTER (
            ALL ( 'employee master' ),
            'employee master'[BM CODE] = _BM
                && [BM-T2 RANK] <> BLANK ()
        )
    )
VAR _OUTPUT =
    IF ( _RANK = 1, _SUM, BLANK () )
RETURN
    _OUTPUT

BM T2 = CALCULATE(SUMX('employee master',[BM_]))

 

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

 

Best Regards,

Ada Wang

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