VLOOKUP most repeated values

[Anonymous]

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?

[CNENFRNL · Accepted solution]

@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]

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.

[CNENFRNL]

Simple enough

[Anonymous]

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?

[Ashish_Mathur]

Hi,

Does this measure work?

Most frequently appearing number = FIRSTNONBLANK ( TOPN ( 1, FILTER(VALUES ( Data[Value] ),Data[value]<>0), [Value count] ), 1 )

[CNENFRNL · Accepted solution]

@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]
)

[Anonymous]

hi @CNENFRNL 

Thanks actually it works. I am not able to get the correct output if there is an additional column as below:

Date Category Value
1/1/2020 A 1
1/1/2020 A 1
1/1/2020 A 1
1/1/2020 A 2
1/1/2020 A 2
1/1/2020 B 3
1/1/2020 B 3
1/1/2020 B 3
1/1/2020 B 4
1/1/2020 B 5
1/1/2020 C 1
1/1/2020 C 2
1/1/2020 C 3
1/1/2020 C 3
1/1/2020 C 3
1/2/2020 A 7
1/2/2020 A 7
1/2/2020 A 7
1/2/2020 A 5
1/2/2020 A 6
1/2/2020 B 4
1/2/2020 B 4
1/2/2020 B 4
1/2/2020 B 4
1/2/2020 B 3
1/2/2020 C 9
1/2/2020 C 8
1/2/2020 C 8
1/2/2020 C 8
1/2/2020 C 5
1/3/2020 A 0
1/3/2020 A 0
1/3/2020 A 0
1/3/2020 A 1
1/3/2020 A 1
1/3/2020 B 2
1/3/2020 B 2
1/3/2020 B 2
1/3/2020 B 3
1/3/2020 B 3
1/3/2020 C 4
1/3/2020 C 4
1/3/2020 C 4
1/3/2020 C 4
1/3/2020 C 1

Desired Output:

Date Category Value
1/1/2020 A 1
1/1/2020 B 3
1/1/2020 C 3
1/2/2020 A 7
1/2/2020 B 4
1/2/2020 C 8
1/3/2020 A 1
1/3/2020 B 2
1/3/2020 C 4

Can you please help me here?

[v-deddai1-msft · Accepted solution]

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

[Anonymous]

@Jihwan_Kim @amitchandak @Ashish_Mathur can you help me?