Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
Hi,
How have you arrived at those number?
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.
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
64 |