Forum Discussion
Top 3 Calculate
I have data of target and sales
1.I want to calculate ASM wise sales the condition is (top 3 flg =y and which target value is top 3 that related sales value want to calculate )
2.BM wise sales the condition is (top 3 flg =y and top 3 target[which target value is top 3 that related sales value want to calculate )
| dealer code | ASO code | ASM code | BM code | target | sales | top 3flg |
| DEALE-01 | ASO-01 | ASM-01 | BM-01 | 1000 | 985 | Y |
| DEALE-02 | ASO-01 | ASM-01 | BM-01 | 3245 | 868 | |
| DEALE-03 | ASO-01 | ASM-01 | BM-01 | 2454 | 8686 | Y |
| DEALE-04 | ASO-01 | ASM-01 | BM-01 | 5375 | 8668 | |
| DEALE-05 | ASO-01 | ASM-01 | BM-01 | 8453 | 6546 | Y |
| DEALE-06 | ASO-02 | ASM-01 | BM-01 | 5353 | 6464 | |
| DEALE-07 | ASO-02 | ASM-01 | BM-01 | 5436 | 5364 | Y |
| DEALE-08 | ASO-02 | ASM-01 | BM-01 | 4324 | 5363 | Y |
| DEALE-09 | ASO-02 | ASM-01 | BM-01 | 9574 | 6464 | |
| DEALE-10 | ASO-02 | ASM-01 | BM-01 | 9575 | 8437 | Y |
| DEALE-11 | ASO-03 | ASM-01 | BM-01 | 8564 | 4443 | Y |
| DEALE-12 | ASO-03 | ASM-01 | BM-01 | 7593 | 5356 | Y |
| DEALE-13 | ASO-03 | ASM-01 | BM-01 | 6345 | 7567 | |
| DEALE-14 | ASO-03 | ASM-01 | BM-01 | 4345 | 4535 | Y |
| DEALE-15 | ASO-03 | ASM-01 | BM-01 | 3425 | 7654 | Y |
| DEALE-16 | ASO-04 | ASM-01 | BM-01 | 3645 | 2345 | |
| DEALE-17 | ASO-04 | ASM-01 | BM-01 | 8675 | 3437 | Y |
| DEALE-18 | ASO-04 | ASM-01 | BM-01 | 5342 | 4645 | |
| DEALE-19 | ASO-04 | ASM-01 | BM-01 | 3545 | 3678 | Y |
| DEALE-20 | ASO-04 | ASM-01 | BM-01 | 4243 | 4267 | Y |
| DEALE-21 | ASO-05 | ASM-01 | BM-01 | 7438 | 8453 | |
| DEALE-22 | ASO-05 | ASM-01 | BM-01 | 9857 | 5353 | y |
| DEALE-23 | ASO-05 | ASM-02 | BM-01 | 9575 | 5436 | |
| DEALE-24 | ASO-05 | ASM-02 | BM-01 | 9585 | 4324 | y |
| DEALE-25 | ASO-05 | ASM-02 | BM-01 | 9474 | 9574 | |
| DEALE-26 | ASO-06 | ASM-02 | BM-01 | 9876 | 9575 | |
| DEALE-27 | ASO-06 | ASM-02 | BM-01 | 7976 | 8564 | y |
| DEALE-28 | ASO-06 | ASM-02 | BM-01 | 8698 | 7593 | |
| DEALE-29 | ASO-06 | ASM-02 | BM-01 | 7969 | 6345 | y |
| DEALE-30 | ASO-06 | ASM-02 | BM-01 | 9797 | 4345 | |
| DEALE-31 | ASO-07 | ASM-02 | BM-01 | 7547 | 8453 | |
| DEALE-32 | ASO-07 | ASM-02 | BM-01 | 9759 | 5353 | y |
| DEALE-33 | ASO-07 | ASM-02 | BM-01 | 8686 | 5436 | |
| DEALE-34 | ASO-07 | ASM-02 | BM-01 | 5000 | 4324 | y |
| DEALE-35 | ASO-07 | ASM-02 | BM-01 | 7595 | 9574 | |
| DEALE-36 | ASO-08 | ASM-02 | BM-01 | 5959 | 9575 | y |
| DEALE-37 | ASO-08 | ASM-02 | BM-01 | 9853 | 8564 | y |
| DEALE-38 | ASO-08 | ASM-02 | BM-01 | 5398 | 7593 | y |
| DEALE-39 | ASO-08 | ASM-02 | BM-01 | 8559 | 6345 | |
| DEALE-40 | ASO-08 | ASM-02 | BM-01 | 9853 | 4345 | y |
5 Replies
rajasekar_o , You can create a measure like
M1= calculate(Sum(Table[sales]), filter(Table, Table[top 3flg] ="y"))
or, in case you want to use other group by in visual
M1= calculate(Sum(Table[sales]), filter(Table, Table[top 3flg] ="y"), allexcept(Table,Table[ASM Code] )
then create TOPN measure
Top 3 = Calculate( [M1], keepfilter(TOPN(3,allselected([Table[ASM Code]), [M1], asc) ))
You can also code the window function
TOPN: https://youtu.be/QIVEFp-QiOk
TOPN with Numeric Parameter -https://youtu.be/cN8AO3_vmlY?t=26448Dynamic TOPN using TOPN/Window and Numeric parameter: https://youtu.be/vm2mdEioQPQ
amitchandak
i want sales amount of top 3 target
as ur given measure didn take a target
ur calculating top3 sales value only
amitchandak
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 =81076i 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
amitchandak
i want Sum(sales) related to the top 3 target valuethe expected out put is bm top 3= 26201
the top 3 flg column already exists in your data?
could you pls provide some details on how to get the output from the sample data?
e.g. how to get 9876? how to get 9861?
