Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

VLOOKUP most repeated values

Hello All,

 

I've a table as below.

 

Category         Value

A                     1

A                     1

A                     1

A                     2

B                     3

B                     3

B                     3

B                     4

B                     4

C                     1

C                     1

C                     1

C                     1

C                     2

C                     2 

 

I have another table as below. I need to VLOOKUP and get the most repeated values in this table.

 

Category         Value

A        

B        

C          

My Desired outcome is as:

 

Category         Value

A                     1

B                     3

C                     1 

Can someone help me to get this output in Power Bi Desktop? Either VLOOKUP or any other possible method?

Need Help
Tips and Tricks
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    Anonymous , you might want to tweak the measure this way,

    Mode =
MAXX (
    TOPN (
        1,
        GROUPBY (
            FILTER ( 'Table', 'Table'[Value] <> 0 ),
            'Table'[Value],
            "cc", COUNTX ( CURRENTGROUP (), 1 )
        ),
        [cc]
    ),
    'Table'[Value]
)
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Anonymous ,

     

    First, CNENFRNL's formula can work well. You can also use the following measure:

    Value1 = 
MAXX (
    TOPN (
        1,
       SUMMARIZE( FILTER('Table','Table'[Value]<>0),
            'Table'[Value],
            "_count", COUNT('Table'[Value])
        ),
        [_count]
    ),
    'Table'[Value]
)

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

     

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, Thank you!! It works but I am facing the below issue.

      Category       Value

      A                     0

      A                     0

      A                     2

      A                     0

      A                     0

      A                     2

      A                     0

      A                     0

      A                     2

       

      Here I need to get 2 as expected output though 0 is the most repeated value. Can you please help me?

      • CNENFRNL's avatar
        CNENFRNL
        Community Champion
        5 years ago

        Anonymous , you might want to tweak the measure this way,

        Mode =
MAXX (
    TOPN (
        1,
        GROUPBY (
            FILTER ( 'Table', 'Table'[Value] <> 0 ),
            'Table'[Value],
            "cc", COUNTX ( CURRENTGROUP (), 1 )
        ),
        [cc]
    ),
    'Table'[Value]
)
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Try these measures:

    Value count = COUNT(Data[Value])
    Most frequently appearing number = FIRSTNONBLANK ( TOPN ( 1, VALUES ( Data[Value] ), [Value count] ), 1 )

    Drag the Category column and Most frequently appearing number measure to the Table visual.