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 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.
Need Help

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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.

  • 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: