Get most talked about item within a group of a group

[warrencowan]

Hi all,

I am trying to get the most talked about item within a group, within a parent group

I have a table as follows that shows the mentions of a phrase within a topic group within a page.

Page	Group name	Phrase	Mentions
a	Graphics cards	Nvidia	310
a	Graphics cards	Asus 1080	21
a	Graphics cards	Intel	10
a	Monitors	HDMI	840
a	Monitors	HD	240
a	Monitors	Wide screen	22
a	TV	Samsung	103
a	TV	Curved	5
a	TV	4k	2
b	Graphics cards	gtx	260
b	Graphics cards	Radeon	110
b	Graphics cards	Intel	20
b	Laptops	hp	1,100
b	Laptops	MSI	210
b	Laptops	tablets	130
b	TV	HD	240
b	TV	Samsung	103
b	TV	1080p	80

What I am trying to do is build a table that consolidates this information to show me the most mentioned phrase within each topic group within each page. A sort of a topn within within kind of thing that will give me this.

Page	Group name	Top Phrase	Mentions	Total mentions for page	% of total
a	Graphics cards	Nvidia	310	341	91%
a	Monitors	HDMI	840	1102	76%
a	TV	Samsung	103	110	94%
b	Graphics cards	gtx	260	390	67%
b	Laptops	hp	1,100	1140	96%
b	TV	HD	240	423	57%

I am familiar with lookups and related, MAX, topN etc, but the grouping within grouping thing has completely thrown me, and I can't get anything to reduce the table down by the grouping levels.

Any ideas. All help warmly appreciated, and rewarded in this life or the next. Both if possible 🙂

Best  Warren

[MFelix · Accepted solution]

Hi @warrencowan,

I have made this in 3 measure in order to have a complete explanation of your table:

Rank = 
RANKX (
    ALL ( Talked[Phrase] ),
    CALCULATE ( SUM ( Talked[Mentions] ) )
)

Place this measure on the visual level filter and select all the values that are equal to 1.

Mentions calc = MAX(Talked[Mentions])

Mentions per page = 
VAR Page = MAX ( Talked[Page] )
VAR Groups = MAX ( Talked[Group name] )

RETURN

    CALCULATE (
        SUM ( Talked[Mentions] ),
        Talked[Page] = Page,
        Talked[Group name] = Groups,
        ALL ( Talked )
    )

Then just add your column to your table, be aware that if you want to have a total line in the table the expression above need to be changed.

The final result is below

Regards,

MFelix

[warrencowan]

Thnx MFelix, that's worked a treat, and I dont think I'd have come up with that in 6 months of Sundays.