Treemap top n per group

[badger123]

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

[v-xicai]

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.

[badger123]

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.