Forum Discussion

J2K's avatar
J2K
Helper I
6 years ago
Solved

Counting Zero-Value Measure Rows in Median Calculations

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...
Need Help
J2K's avatar
J2K
Helper I
6 years ago

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's avatar
J2K
Helper I
6 years ago

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.