cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
warrencowan Regular Visitor
Regular Visitor

Get most talked about item within a group of a group

 

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.
 
PageGroup namePhraseMentions
aGraphics cardsNvidia310
aGraphics cardsAsus 108021
aGraphics cardsIntel10
aMonitorsHDMI840
aMonitorsHD240
aMonitorsWide screen22
aTVSamsung103
aTVCurved5
aTV4k2
bGraphics cardsgtx260
bGraphics cardsRadeon110
bGraphics cardsIntel20
bLaptopshp1,100
bLaptopsMSI210
bLaptopstablets130
bTVHD240
bTVSamsung103
bTV1080p80
 
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.
 
PageGroup nameTop PhraseMentionsTotal mentions for page% of total
aGraphics cardsNvidia31034191%
aMonitorsHDMI840110276%
aTVSamsung10311094%
bGraphics cardsgtx26039067%
bLaptopshp1,100114096%
bTVHD24042357%

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Get most talked about item within a group of a group

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

 

ranking.png

 

Regards,

MFelix

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
Super User
Super User

Re: Get most talked about item within a group of a group

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

 

ranking.png

 

Regards,

MFelix

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

warrencowan Regular Visitor
Regular Visitor

Re: Get most talked about item within a group of a group

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 307 members 2,831 guests