Forum Discussion
Top 3 Concatenated
Hi All,
Thanks in advance.
| Date Table | ||
| DateKey | Date | Week |
| 20190101 | 01/01/2019 | 1 |
| x | x | x |
| x | x | x |
| x | x | x |
| 20190108 | 08/01/2019 | 2 |
| Food Table | ||
| DateKey | Food | Amount |
| 20190101 | Banana | 500 |
| 20190101 | Orange | 400 |
| 20190101 | Apple | 300 |
| 20190101 | Pear | 200 |
| 20190101 | Grape | 100 |
| 20190108 | Nuts | 1000 |
| 20190108 | Cake | 900 |
| 20190108 | Banana | 800 |
| 20190108 | Apple | 700 |
| 20190108 | Pear | 600 |
| Desired Output | ||
| Week | Food_Top 3 | Amount |
| 1 | Banana,Orange,Apple | 1500 |
| 2 | Nuts,Cake,Banana | 4000 |
Appreciate your efforts. Thanks
Anonymous
You can try this
measure = VAR tbl=ADDCOLUMNS('Table',"rank",RANKX(FILTER('Table','Table'[Week]=EARLIER('Table'[Week])),'Table'[Amount],,DESC)) return CONCATENATEX(FILTER(tbl,[rank]<=3),'Table'[Food],",",[rank],ASC)
13 Replies
Anonymous , Try this , I have not tested
concatenatex(TOPN(3,ALLSELECTED(Table[Food]),calculate(sum(Table[Amount])),dense), [Food])
Hi amitchandak
Thanks for the effort.The formula seems to be incomplete, perhaps I'm missing something.
Just to be clear, the second table is also just three columns, "Week, Amount, Food_Top3".
Anonymous
You can try this
measure = VAR tbl=ADDCOLUMNS('Table',"rank",RANKX(FILTER('Table','Table'[Week]=EARLIER('Table'[Week])),'Table'[Amount],,DESC)) return CONCATENATEX(FILTER(tbl,[rank]<=3),'Table'[Food],",",[rank],ASC)Hi ryan_mayu
Much appreciated.It's returning more than the Top 3. It's returning almost everything instead of just the Top 3.
For more context, my data model
Date (Date, Datekey, Week No)Fact_Table(Datekey, Amount, Food)
Anonymous
you need to turn off total
pls see the attachment below
Anonymous here it is :
Top 3 Food = VAR __table = TOPN ( 3, ALLEXCEPT ('Table','Table'[Week] ), [Sum], DESC ) RETURN CONCATENATEX ( __table, [Food], "," )✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
parry2k
Thanks.Still not working. I've updated the original post with the exact data model, perhpas that'll help.
Anonymous it should work, just change the columns:
Top 3 Food = VAR __table = TOPN ( 3, ALLEXCEPT ('Table','Table'[DateKey] ), [Sum], DESC ) RETURN CONCATENATEX ( __table, [Food], "," )✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Anonymous solution attached, tweak it as you see fit.
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Hi Anonymous
Have you solved this question? You can try the solution shared by parry2k. If you need more help, please let me know. If you have solved the question, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
