Forum Discussion
Helper VTop 2 sales
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
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.
2 Replies
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.- Ashish_Mathur
Super User
Hi,
How have you arrived at those number?
