cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wowziewoo
Helper II
Helper II

Need name of category with the most values

I have a table of clients and their genders, and I need to identify the gender with the most values, so from a table like this: 

 

Client 1 - Male

Client 2 - Male

Client 3 - Female

Client 4 - Male

Client 5 - Male

Client 6 - Male

 

I need to produce the word "Male" as a measure, because there are 5 males and only 1 female. I can't use visual filters to achieve this. 


Using RANK, I got Male = 1 and Female = 2, but I'm not sure how to take that output and produce the word "Male". 

 

What is wrong with my approach? Thank you!

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @wowziewoo 

Please try

Max Gender =
MAXX (
    TOPN (
        1,
        SUMMARIZE ( 'Table', 'Table'[Gender], "@Count", COUNTROWS ( 'Table' ) ),
        [@Count]
    ),
    'Table'[Gender]
)

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

Hi @wowziewoo 

Please try

Max Gender =
MAXX (
    TOPN (
        1,
        SUMMARIZE ( 'Table', 'Table'[Gender], "@Count", COUNTROWS ( 'Table' ) ),
        [@Count]
    ),
    'Table'[Gender]
)

This worked great - I was able to even add a filter on to COUNTROWS to remove blanks from consideration. Thanks so much! 

 

Didn't try the other option, that might work as well. 

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

Jihwan_Kim_0-1664987739986.png

 

 

expected result measure: =
VAR _gendertablewithcounting =
    ADDCOLUMNS (
        DISTINCT ( Data[Gender] ),
        "@count", CALCULATE ( COUNTROWS ( Data ) )
    )
VAR _groupbymaxcounting =
    GROUPBY (
        _gendertablewithcounting,
        Data[Gender],
        "@maxcount", MAXX ( CURRENTGROUP (), [@count] )
    )
RETURN
    MAXX ( _groupbymaxcounting, Data[Gender] )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
Winner of T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Find out who won the T-Shirt Design Challenge and the top 3 finalists.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.