# How to write code for the MODE function in Powerpivot?



## lmgarzio (Jul 10, 2013)

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 (Jul 10, 2013)

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 (Nov 20, 2013)

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.


----------

