Median and Distinct

[spuri_78]

Hi all,

I'm having difficultiues calculating median of employee sick hours. The measure I have in place is 

Sick Hours Median = MEDIAN('T Sick'[SumOfSick Hours]).

The table has Emp Id, Surname, First Name, Org Unit, Division, Branch, Report Group, Unit and Sum of Sick Hrs.

However, it's not giving me the correct value. The table has multiple Emp Ids and it is adding up the hours. In excel, when I pivot on emp id and sick hours and then do the median, it gives me the correct value.

Any help with this would be greatly appreciated!

[Shravan133 · Accepted solution]

Try this:

Sick Hours Median =
MEDIANX(
VALUES('T Sick'[Emp Id]),
CALCULATE(SUM('T Sick'[SumOfSick Hours]))
)

[spuri_78]

Absolute legend, my friend - mind explaining the formula a bit?

[Shravan133]

• VALUES('T Sick'[Emp Id]) ensures that we are calculating the median across distinct employees.
• CALCULATE(SUM('T Sick'[SumOfSick Hours])) calculates the sum of sick hours for each employee.
• MEDIANX then calculates the median across those individual sums for each employee.