cancel
Showing results for
Did you mean:
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.

 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!

1 ACCEPTED SOLUTION
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"
)
``````

Best regards

Icey

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

5 REPLIES 5
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"
)
``````

Best regards

Icey

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

Frequent Visitor

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

Frequent Visitor

Hi

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

Frequent Visitor

Hi

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

Announcements

#### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors