Calculating the Mode and Median for Non-numeric data

[SusuYes]

Hello everyone, 

I have data on the number of staff who attended certain workshops. I have calculated the average number of attendance to every workshop type. However, I want to calculate the mode and the mean as well.

My data is stored as follows: 

Workshop Type	Staff ID Attended
Type A	1111
Type B	3333
Type A	4444
Type A	5555
Type C	2222

I have tried using the formulas on this page however I cannot seem to get a result. 

I am hoping to get the following:

• For every workshop type, what is the mode of staff attended? 
• For every workshop type, what is the median nuber of staff attended?

Any help or guidance is appreciated. 

[Ashish_Mathur]

Hi,

Write these measures.  Based on that data that you have shared, please share the exact values that you are expecting of the Mode.

Attendees = SUM(Data[Staff ID Attended])

Median = MEDIANX(VALUES(Data[Staff ID Attended]),[Attendees])

Hope this helps.

[SusuYes]

Hello Ashish, thanks for your suggestion but unfortunatly my Staff ID coloumn is stored as a string so I cannot sum it. Any idea how to get across that? 

[Ashish_Mathur]

You are welcome.  In which case, is your question even relevant?  I thought arithmatic functions (specially Median and Mode) work only on numeric columns.

[SusuYes]

Hello Ashish, I am hoping to report on the number of attendees to each workshop type and finding the median, mean, and mode of the attendance for each workshop type. 

Would using a count for Attendees serve the same purpose? 

[Ashish_Mathur]

Just show the exact result you are expecting.

[amitchandak]

@SusuYes , Try like

Medianx(Values(Table[Workshop Type]), calculate(Count(Table[Staff ID])) )

Averagex(Values(Table[Workshop Type]), calculate(Count(Table[Staff ID])) )

[SusuYes]

Hello @amitchandak, 

thanks for your response. I tried the above formulas but for the Medianx I got the result back to be 1 which is not quite right. I am expecting to know for each workshop type, what is the median value of the number of staff that attended.