Top 2 sales

[rajasekar_o]

hi team,
i have the sales data 
I  Want to  calculate
1.ASO top 2 sales based on  top2 target value  the top 2 target is take only if t20 is 'Y'
2.ASM top 2 sales based on  top2 target value  the top 2 target is take only if t20 is 'Y'
3.BM top 2 sales based on  top2 target value  the top 2 target is take only if t20 is 'Y'

DEALERCODE	ASO CODE	ASM CODE	BM CODE	sales	TARGET	t20
DEALE-01	ASO-01	ASM-01	BM-01	38988	39000	Y
DEALE-02	ASO-01	ASM-01	BM-01	3488	3500	Y
DEALE-03	ASO-01	ASM-01	BM-01	4488	4500
DEALE-04	ASO-01	ASM-01	BM-01	7588	7600	Y
DEALE-05	ASO-01	ASM-01	BM-01	3488	3500
DEALE-06	ASO-02	ASM-01	BM-01	12088	12100
DEALE-07	ASO-02	ASM-01	BM-01	11088	11100	Y
DEALE-08	ASO-02	ASM-01	BM-01	15588	15600	Y
DEALE-09	ASO-02	ASM-01	BM-01	23188	23200	Y
DEALE-10	ASO-02	ASM-01	BM-01	26688	26700
DEALE-11	ASO-03	ASM-02	BM-01	38788	38800	Y
DEALE-12	ASO-03	ASM-02	BM-01	49888	49900	Y
DEALE-13	ASO-03	ASM-02	BM-01	12088	12100	Y
DEALE-14	ASO-03	ASM-02	BM-01	12548	12560
DEALE-15	ASO-03	ASM-02	BM-01	16388	16400
DEALE-16	ASO-04	ASM-02	BM-01	18308	18320
DEALE-17	ASO-04	ASM-02	BM-01	21438	21450
DEALE-18	ASO-04	ASM-02	BM-01	31188	31200	Y
DEALE-19	ASO-04	ASM-02	BM-01	44988	45000	Y
DEALE-20	ASO-04	ASM-02	BM-01	6488	6500	Y

THE FINAL OUT PUT 
ASO TOP 2 SALES =261304
ASM TOP 2 SALES=157052
BM TOP 2 SALES =81076

i try calculation for ASO TOP 2 SALES ,ASM TOP 2 SALES,BM TOP 2 SALES  its show same value for all.
how to calculate this 

[Ashish_Mathur]

Hi,

How have you arrived at those number?

[v-binbinyu-msft · Accepted solution]

Hi @rajasekar_o ,

Please try to create measure with below dax formula:

ASM TOP2 =
VAR tmp =
    FILTER ( 'Table', [t20] = "Y" )
VAR tmp1 =
    ADDCOLUMNS (
        tmp,
        "RK", RANK (, tmp, ORDERBY ( [sales], DESC ), PARTITIONBY ( 'Table'[ASM CODE] ) )
    )
VAR tmp2 =
    FILTER ( tmp1, [RK] IN { 1, 2 } )
RETURN
    SUMX ( tmp2, [sales] )

ASO TOP2 =
VAR tmp =
    FILTER ( 'Table', [t20] = "Y" )
VAR tmp1 =
    ADDCOLUMNS (
        tmp,
        "RK", RANK (, tmp, ORDERBY ( [sales], DESC ), PARTITIONBY ( 'Table'[ASO CODE] ) )
    )
VAR tmp2 =
    FILTER ( tmp1, [RK] IN { 1, 2 } )
RETURN
    SUMX ( tmp2, [sales] )

BM T2 =
VAR tmp =
    FILTER ( 'Table', [t20] = "Y" )
VAR tmp1 =
    ADDCOLUMNS (
        tmp,
        "RK", RANK (, tmp, ORDERBY ( [sales], DESC ), PARTITIONBY ( 'Table'[BM CODE] ) )
    )
VAR tmp2 =
    FILTER ( tmp1, [RK] IN { 1, 2 } )
RETURN
    SUMX ( tmp2, [sales] )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.