cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
birdietran
Frequent Visitor

[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!

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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"
)

RANK.JPG

rank - without dense.JPG

 

 

Best regards

Icey

 

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

View solution in original post

5 REPLIES 5
Icey
Community Support
Community Support

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"
)

RANK.JPG

rank - without dense.JPG

 

 

Best regards

Icey

 

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

View solution in original post

birdietran
Frequent Visitor

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

 

 

birdietran
Frequent Visitor

Hi

 

Anyone can help please :<?

PC2790
Solution Sage
Solution Sage

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])

 

Hi

 

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

 

birdietran_0-1611049240421.png

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

html:
Carousel MBAS ARun Part 1 768x460

MBAS 2021

Join Arun Ulagaratchagan and the Power BI team at MBAS! #MSBizAppsSummit #CommunityRocks

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

Top Solution Authors