Forum Discussion

birdietran's avatar
birdietran
Frequent Visitor
5 years ago
Solved

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

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.
 
CCYAmount_in_USD

Top_3_CCY

(desired)

Top_3_CCY

(actual)

USD10031USDUSD
USD120USDUSD
JPY3440JPYOthers
EUR534OthersOthers
GBP1297OthersOthers
KRW30000KRWKRW

 

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!

Need Help
  • Icey's avatar
    Icey
    5 years ago

    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.

5 Replies

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    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's avatar
      birdietran
      Frequent Visitor
      5 years ago

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

       

       

  • PC2790's avatar
    PC2790
    Community Champion
    5 years ago

    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's avatar
      birdietran
      Frequent Visitor
      5 years ago

      Hi

       

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