[HELP!] Identify top 3 values summed based on another column

[birdietran]

Hi all

I have a data table containing of the first 2 columns as below want to create a column chart showing: The top 3 CCY with highest lump sum amount while the remaining CCYs are accummulated under 1 column named "Others". My solution is to create the third column named Top_3_CCY with below code and use it as axis labels and legend for my chart.

Top_3_CCY =

if(

RANKX('Outward Overseas', CALCULATE (SUM('Outward Overseas'[Txn Amount in USD]) , ALLEXCEPT ( 'Outward Overseas', 'Outward Overseas'[CCY]))) <=3,'Outward Overseas'[CCY],

"Others")

The result is not what I expected (see the highlighted). The reason lies in the RANKX function: while it correctly returns 1 & 2 for the top 2 CCY (USD & KRW), the 3rd highest value in JPY is ranked as 4 - hence it is included in the Others category.

CCY	Amount_in_USD	Top_3_CCY (desired)	Top_3_CCY (actual)
USD	10031	USD	USD
USD	120	USD	USD
JPY	3440	JPY	Others
EUR	534	Others	Others
GBP	1297	Others	Others
KRW	30000	KRW	KRW

1. Is there any better solution to the original problem?

2. If yes, what should I amend in the code to get the desired outcome?

Thank you so much in advance!

[Icey · Accepted solution]

Hi @birdietran ,

Try this:

Top_3_CCY =
IF (
    RANKX (
        'Outward Overseas',
        CALCULATE (
            SUM ( 'Outward Overseas'[Txn Amount in USD] ),
            ALLEXCEPT ( 'Outward Overseas', 'Outward Overseas'[CCY] )
        ),
        ,
        DESC,
        DENSE
    ) <= 3,
    'Outward Overseas'[CCY],
    "Others"
)

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[birdietran]

This is exactly what I need! Thank you so much!

[birdietran]

Hi

Anyone can help please :<?

[PC2790]

Try this if it works:

Top3 = SumX(TOPN(3,SUMMARIZE('Outward Overseas','Outward Overseas'[CCY],"ColumnName",[Txn Amount in USD]),[ColumnName],DESC),[Txn Amount in USD])

[birdietran]

Hi

Thank you for your help. It didnt work though. I tried add a Top3 column and below is what I got :< :