How to write code for the MODE function in Powerpivot?

[lmgarzio]

Hello, I am baffled by the fact that MODE is not a recognized function in Powerpivot. Can someone help me write code for the MODE function so that it can be calculated in Powerpivot as a Calculated Field?

Thank you!

 

[MD610]

I think you would probably need 2 Calculated Columns to get this:

1. To count recurrence
2. Find the max of 1.

TableName[ColumnCnt]=CALCULATE(
                                         COUNT([ColumnName]),
                                         FILTER(TableName,
                                               TableName[ColumnName] = EARLIER(TableName[ColumnName])
                                          )
                                      )

TableName[Mode]=MAX([ColumnCnt])=[ColumnCnt]

The TRUE in TableName[Mode] is your answer.

 

[MD610]

This thread is a little old but I was revisiting it for another PowerPivot problem that needs to show the MODE. I needed a dynamic version that was done with a measure and not a calculated column. Looks like this is what the OP was asking for the first time but I misunderstood.

Anyway, thought I would update this with the Measure/Calc Field version that I am using to identify the MODE in case it could help others:

=IF(
      CALCULATE(
              MAXX(
                     VALUES(TableName[Data]),
                            CALCULATE(
                                     COUNTROWS(TableName)
                             )
                     ),
               ALLSELECTED(TableName)
      )
      = COUNTROWS(TableName),
      "MODE",
      BLANK()
 )

With my data set in Pivot Rows, this is working well for me and changes dynamically with slicer selections.