How to obtain the most common value from a column and display it?

[Daemetius]

Greetings,

I was wondering if there was a way to display the most common value in a column? For example:

Reported_Users

UserA

UserB

UserC

UserA

UserA

UserA

Here UserA is the most common user. So I was thinking of trying to display it with a  "Card" visual.

I think I read somewhere it's possible with DAX? I'm open to any other methods, if any.

Thank you.

P.S: I'm relatively new to the program.

[Reid_Havens · Accepted solution]

Hi Daemetius.

So there's a way to do this using Top N, First Non Blank, and Rank X. 

The first expression would be to generate a row count from the table that has that data:

User Count = 
COUNTROWS( TableName )

Then to return a text value for a card you can use this formula below. This ranks the users by row count and returns the top row.

Top User = 
FIRSTNONBLANK (
    TOPN (
        1, 
        VALUES ( UserTableName[UserColumnName] ), 
        RANKX( ALL( UserTableName[UserColumnName] ), [Row Count],,ASC)
    ), 
    1 
)

More info about this can be found on this SQLBI Post. 🙂

Reid Havens - Owner

Havens Consulting Inc.

[Anonymous]

Hi @Reid_Havens,

Thanks for your response. In my case, User A and User B have the same occurence, but they are from different departments (Sales and Audit, respectively). When I used your solution and use card visual, it displays User A (is it because of alphabetical order?). Then, when I apply "Audit" filter, it still displays User A. What am I missing?

Thanks!

[aparnavjyn]

Hi,

Your solution worked for finding the most common column. I have a requirement where I need to display the 3 most common values, in different columns. ie, by creating a measure for the 2nd and 3rd common value. Any suggestions on this? 

The problem I face is when the first value was blank, the same value repeats when I change the number for TOPN.

Top_First ActionItem = CALCULATE(FIRSTNONBLANK(
TOPN(
1,
(VALUES(DB_CX_2018_V6_V2_New[Action Items])),

RANKX(ALL(DB_CX_2018_V6_V2_New[Action Items]),[Count_Action Items],,ASC)),1),
FILTER(DB_CX_2018_V6_V2_New,DB_CX_2018_V6_V2_New[Survey_Year]="2019"))

[Daemetius]

---

@Reid_Havens wrote:

Hi Daemetius.

So there's a way to do this using Top N, First Non Blank, and Rank X. 

The first expression would be to generate a row count from the table that has that data:

User Count = 
COUNTROWS( TableName )

Then to return a text value for a card you can use this formula below. This ranks the users by row count and returns the top row.

Top User = 
FIRSTNONBLANK (
    TOPN (
        1, 
        VALUES ( UserTableName[UserColumnName] ), 
        RANKX( ALL( UserTableName[UserColumnName] ), [Row Count],,ASC)
    ), 
    1 
)

More info about this can be found on this SQLBI Post. 🙂

Reid Havens - Owner

Havens Consulting Inc.

---

What is the [Row Count] for? I'm trying to implement this but I get the red underline error on:

RANKX( ALL( UserTableName[UserColumnName] ), [Row Count],,ASC)

System is stating that it can't find it or may not be used in this expression

[Reid_Havens]

Apologies, forgot to update the name in that one. [Row Count] is supposed to point to the [User Count] Measure. Just update that and you should be good.

[GuillemXII]

Sorry, but when I introduce the code, the equivalent for [User Count] gets underlained in red and tells me that "Argument '3' in ALL function is required'. Which could be the error here?:

1. M.PST = 
FIRSTNONBLANK(
    TOPN(
        1;
        VALUES('Datos Numericos'[1.PST Compromiso]);
        RANKX(ALL('Datos Numericos'[1.PST Compromiso];[Nº Filas];;ASC)
     );
     1        
 )

Where [Nº Filas] = COUNTROWS('Datos Numericos')

[strachi]

How can this be done without using measures?