Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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!
Solved! Go to 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.
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.
This is exactly what I need! Thank you so much!
Hi
Anyone can help please :<?
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 :< :
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.