Share via

PowerPivot - A sample of custom Median & Mode function

  • Article

Please note the performance is not optimized for a very large dataset, try with your own data.

Create a Median Meausre

[Lowerbound] =MINX( FILTER( VALUES( BoxOffice[TicketsSold]), CALCULATE( COUNTROWS( BoxOffice ), BoxOffice[TicketsSold] <= EARLIER( BoxOffice[TicketsSold] ) )  >= COUNTROWS( BoxOffice ) / 2 ), BoxOffice[TicketsSold])

[Upperbound]=MINX( FILTER( VALUES( BoxOffice[TicketsSold]), CALCULATE( COUNTROWS( BoxOffice ), BoxOffice[TicketsSold] <= EARLIER( BoxOffice[TicketsSold] ) )  > COUNTROWS( BoxOffice ) / 2 ), BoxOffice[TicketsSold])

[Median TicketsSold] = ([Lowerbound]+ [Upperbound]) / 2

*BoxOffice: A PowerPivot Table
*TicketsSold: The column that you want to calculate the median in BoxOffice

Create a Mode Measure

[ModeCount] =IF(COUNTROWS(VALUES(BoxOffice[MediaId]))=1, COUNTROWS(FILTER(ALL(BoxOffice), BoxOffice[MediaId]=VALUES(BoxOffice[MediaId]))), BLANK() )

[Mode MediaId] = MINX(FILTER(ALL(BoxOffice[MediaId]),[ModeCount]=MAXX(All(BoxOffice),[ModeCount])),BoxOffice[MediaId])

*BoxOffice: A PowerPivot Table
*MediaId: The column that you want to calculate the mode in BoxOffice