Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
warrencowan
Helper I
Helper I

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
MFelix
Super User
Super User

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

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

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

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.