How to know which value repeats the most in every product I have?

[Gustavo91Peru]

Hi guys,

I´m having issues with the following topic.

I have products that are related to different clients, and they have opinions about them.

Example: 

PRODUCTO	CLIENTE	OPINION
A	1	GOOD
A	2	GOOD
A	3	BAD
B	4	GREAT
B	5	GREAT
B	6	GOOD
C	7	LAME
C	8	PERFECT
C	9	PERFECT

How can I extract which opinion @repeats the most in each product in a dashboard im doing??

Thanks in advance!

[camargos88 · Accepted solution]

Hi @Gustavo91Peru ,

My previous measure is wrong, it's calculating based on alphabetical values.

I tried a new one and handling ties:

Measure =

VAR _tbl = SELECTCOLUMNS(TOPN(1; SUMMARIZE('Table'; 'Table'[PRODUCTO]; 'Table'[OPINION]; "Count"; COUNT('Table'[OPINION])); [Count]; DESC); "Opinion"; 'Table'[OPINION])

RETURN IF(COUNTROWS(_tbl) > 1; CONCATENATEX(_tbl; [Opinion]; ";"); _tbl)

Ricardo

[Greg_Deckler]

Good thing I created MODE: https://community.powerbi.com/t5/Quick-Measures-Gallery/Mode-Single-Column/m-p/359150#M101

[camargos88]

Hi @Gustavo91Peru 

You can use this code to create a measure:

Measure =

VAR _tbl = MAXX(SUMMARIZE('Table'; 'Table'[PRODUCTO]; 'Table'[OPINION]; "Count"; COUNT('Table'[OPINION])); 'Table'[OPINION])

RETURN _tbl

You can remove the total from table visual.

The measure above is wrong, this one is correct:

Measure =

VAR _tbl = SELECTCOLUMNS(TOPN(1; SUMMARIZE('Table'; 'Table'[PRODUCTO]; 'Table'[OPINION]; "Count"; COUNT('Table'[OPINION])); [Count]; DESC); "Opinion"; 'Table'[OPINION])

RETURN IF(COUNTROWS(_tbl) > 1; CONCATENATEX(_tbl; [Opinion]; ";"); _tbl)

Ricardo