Most common for category

[Anonymous]

Hi,

I need a New Column like this "Mostcommon type" based on each user

User	type	Mostcommon type
User 1	A	A
User 1	A	A
User 1	B	A
User 2	A	B
User 2	B	B
User 2	B	B

Thanks!

[petrovnikitamai]

try to add two columns:

1. Calculate count match types by user

MostPopularType = COUNTX(
    FILTER(TableName;
        EARLIER(TableName[User])=TableName[User] &&
        EARLIER(TableName[type])=TableName[type]);
    TableName[type])

2. Return most popular type

Mostcommon type(check) = 
var mmax = 
MAXX(
    FILTER(TableName;
        EARLIER(TableName[User])=TableName[User]);
    TableName[MostPopularType])
return
LOOKUPVALUE(TableName[type];TableName[User];TableName[User];TableName[MostPopularType];mmax)

i get:

mb someone knows the best way calculate it

[Anonymous]

Hi @petrovnikitamai ,

Thank you,

First column works great, for second one i get error message:

My real dataset contains a lot more values...

[Zubair_Muhammad · Accepted solution]

@Anonymous 

How about this calculated column

Calc Column =
CONCATENATEX (
    CALCULATETABLE (
        TOPN ( 1, VALUES ( Table1[type] ), CALCULATE ( COUNT ( Table1[type] ) ), DESC ),
        ALLEXCEPT ( Table1, Table1[User] )
    ),
    [type],
    ","
)

[parry2k]

@Anonymous is the criteria of getting most common type is based on count of types for each user?

[Anonymous]

@parry2k ,

exactly