Forum Discussion

spuri_78's avatar
spuri_78
Regular Visitor
2 years ago
Solved

Median and Distinct

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!
Need Help
  • Shravan133's avatar
    Shravan133
    2 years ago

    Try this:

     

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

3 Replies

  • Shravan133's avatar
    Shravan133
    Icon for Super User rankSuper User
    2 years ago

    Try this:

     

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

    • spuri_78's avatar
      spuri_78
      Regular Visitor
      2 years ago

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

      • Shravan133's avatar
        Shravan133
        Icon for Super User rankSuper User
        2 years ago

         

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