Counting Zero-Value Measure Rows in Median Calculations

[J2K]

I need the median value of a column (whose value is derived from a measure).  In every attempt, it is only returning the median of rows that have a value greater than zero.

I have two tables, one is an "employee" table and one an "attendee" table. I have a measure that counts each time an employee is in the attendee table:

EE Attend = CALCULATE(COUNTROWS('Attendee_R'), FILTER('Attendee_R', 'Attendee_R'[Employee ID] = MAX(Employee_M[Employee ID]) )) + 0

In order to use this properly in a visual table (and receive a total at the bottom) I use this measure in the visual itself:

EE Attend TOT = SUMX(Employee_M,[EE Attend])

This allows me to use a slicer on dates, employee regions, etc., and the visual will filter based on their attendance over the selected criteria.  I need to be able to get the average and median of each column.  When I try to do that, I get the following:

The measure I am using to calculte averages is correct, however, the median measure ignores the zero rows and only returns a value for the rows with a number.  I have tried several different ways that I can think of to get the median, but none of them work correctly.  How would I calculate the median of the full column (including zero rows)?  Any help is greatly appreciated.

Thanks!

-Jason K.

[J2K]

Hi All,

Thank you for your replies.  My numbers are not coming from a static table (as in @Greg_Deckler's .pbix), they are being generated by a counting measure. I would love to post a .pbix file to show this, but it does not look like I can. I have no option to add an attachment to my posts/replies.

Edit: Here are some screen shots that I hope will help explain this further (note that these numbers will be different from my original post).

Here are the two tables:

Here is the relationship:

The visual is a table, with two columns, the first being Employee_M[Employee ID], and the second being this measure:

EE Attend TOT = SUMX(Employee_M,[EE Attend])

The second part of that measure uses this measure:

EE Attend = CALCULATE(COUNTROWS('Attendee_R'), FILTER('Attendee_R', 'Attendee_R'[Employee ID] = MAX(Employee_M[Employee ID]) )) + 0

My median measure is:

MED Col A = MEDIANX(SUMMARIZE('Attendee_R',Employee_M[Employee ID]),[EE Attend])

This is the result:

[J2K]

I should mention that the reason why I am not using a calculated column is that I need to be able to filter by dates.

[Greg_Deckler]

@J2K  Most people use OneDrive or Box or some other file sharing service to share PBIX files.

[J2K]

@Greg_Deckler Here you go:

https://www.dropbox.com/sh/e9nhfa9ct8lvgdt/AABA4QgxXplpOARmOqiK4qlpa?dl=0&preview=MedianDemo.pbix

Again, I appreciate you taking the time to look at these things.

[J2K]

@Greg_Deckler Here you go:

https://www.dropbox.com/sh/e9nhfa9ct8lvgdt/AABA4QgxXplpOARmOqiK4qlpa?dl=0&preview=MedianDemo.pbix 

Again, I appreciate that you take the time to look at these things.

[v-gizhi-msft · Accepted solution]

Hi,

Please try this measure:

MED Col A = MEDIANX(SUMMARIZE(Employee_M,Employee_M[Employee ID],"EE",[EE Attend]),[EE])

The result shows:

See my attached pbix file.

Best Regards,

Giotto

[J2K]

Hi Giotto,

Thank you very much. This works perfectly.

[v-gizhi-msft]

Hi,

Please try to use MEDIAN function.

Like this:

Best Regards,

Giotto

[Greg_Deckler]

Well, all I can say from the information provided is that you are doing something wrong in your calculation. Because MEDIAN and MEDIANX work for your data. See attached PBIX.