cancel
Showing results for 
Search instead for 
Did you mean: 

Mode - Multiple Columns

Super User
434 Views
Highlighted
Super User
Super User

Mode - Multiple Columns

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490... you have a table of data with multiple columns like this...

 

M1         M2          M3        M4         Index

AAA3 AAA3 AAA3 AAA3 0
BBB3 CCC3   CCC3 1
      DDD2 2

 

...and you wish to figure out what value occurs most often in those columns. You can use a measure like this:

 

 

Mode = VAR myTable = UNION(UNION(UNION(SUMMARIZE(Mode,Mode[Index],"Methods",MAX(Mode[M1])),SUMMARIZE(Mode,Mode[Index],"Methods",MAX(Mode[M2]))),SUMMARIZE(Mode,Mode[Index],"Methods",MAX(Mode[M3]))),SUMMARIZE(Mode,Mode[Index],"Methods",MAX(Mode[M4])))
VAR myTable1 = FILTER(GROUPBY(myTAble,[Index],[Methods],"Count",COUNTX(CURRENTGROUP(),[Methods])),[Methods]<>"")
VAR myTable2 = FILTER(myTable1,[Count]=MAXX(myTable1,[Count]))
VAR Mode1 = MAXX(LASTNONBLANK(myTable2,[Methods]),[Methods])
RETURN Mode1

This quick measure would take as input a unique identification column, and the columns in which to calculate the mode.

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!