Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I'm using treemap visual with category for Group, name for Details and sum of phrase volumes for Values. I am trying to show the top 10 names per group (category) by volume. However, when I set top n, it doesn't work at a group level, it filters to the top 10 across all groups. Is there any way to achieve this?
Here is a sample of my tables and data. The tables are joined by phrase.
Table1 | ||
Name | Phrase | Category |
name 1 | phrase 1 | A |
name 1 | phrase 2 | A |
name 1 | phrase 2 | B |
name 1 | phrase 3 | C |
name 2 | phrase 4 | A |
name 2 | phrase 4 | B |
name 3 | phrase 4 | A |
name 3 | phrase 4 | B |
name 3 | phrase 5 | A |
name 3 | phrase 5 | B |
name 3 | phrase 5 | C |
Table2 | |
Phrase | Volume |
phrase 1 | 10 |
phrase 2 | 20 |
phrase 3 | 30 |
phrase 4 | 40 |
phrase 5 | 5 |
Desired output, example top 1 per group:
A | phrase 4 |
B | phrase 4 |
C | phrase 3 |
Hi @badger123 ,
You can create measure Top N in table1.
Top N = CALCULATE(MAXX(TOPN(1,FILTER(Table2,MAX(Table2[Phrase])=MAX(Table1[Phrase])),Table2[Volume],DESC),Table2[Phrase]),FILTER(ALLSELECTED(Table1),Table1[Category]=MAX(Table1[Category])))
Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EUC2yuOyRGtMtG-VRO...
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xicai
Thank you! This works perfectly, but I am trying to achieve the following:
Desired output:
A | name 3 |
B | name 3 |
C | name 1 |
Sorry, I got it wrong in my original note! Any thoughts on how to do this? I also only provided a sample of my tables, but I would like to be able to calculate top 10 names per category.
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
104 | |
93 | |
83 | |
64 |