Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Most common for category

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...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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],
    ","
)
petrovnikitamai's avatar
petrovnikitamai
Resolver V
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Zubair_Muhammad
      Community Champion
      7 years ago

      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],
    ","
)