cancel
Showing results for
Did you mean:
Helper III

## Finding the averages of different halves of a data set

Hi, I wish to find a "trajectory" score for each individual. I have a dataset that has a score recorded for an individual during a particular time. I would like to find the average score for the first half of the data (the values achieved before the median of the time scores) and then the average score for the second half of the data by time. Then find the difference which will be shown in a card after someone is selected by a slicer. The table is called Track Data.

I have tried:

average of first half = calculate(AVERAGE(value),'Track Data'[Number Time]<=median('Track Data'[Number Time]))
I think I can see why this fails but not sure about the fix.
Here is a small sample of the data:

 Value Person Number Time 6 1 1 91 2 1 58 3 1 25 4 1 58 7 1 35 1 2 34 2 2 12 3 2 81 4 2 6 6 2 61 3 3 83 4 3 75 5 3 17 6 3 9 7 3
1 ACCEPTED SOLUTION
Super User

Hi @IHam ,

Let me know if this is what is needed.

Create Measures

``Median = CALCULATE(Median(Table2[Value]), ALLEXCEPT(Table2,Table2[Person]))``
``Average First HAlf = CALCULATE(AVERAGE(Table2[Value]), FILTER(ALLEXCEPT(Table2,Table2[Person]),Table2[Value] < [Median]))``
``Average Second HAlf = CALCULATE(AVERAGE(Table2[Value]), FILTER(ALLEXCEPT(Table2,Table2[Person]),Table2[Value] >= [Median]))``
``Divide Val = DIVIDE([Average First HAlf],[Average Second HAlf],0)``

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

2 REPLIES 2
Super User

Hi @IHam ,

Let me know if this is what is needed.

Create Measures

``Median = CALCULATE(Median(Table2[Value]), ALLEXCEPT(Table2,Table2[Person]))``
``Average First HAlf = CALCULATE(AVERAGE(Table2[Value]), FILTER(ALLEXCEPT(Table2,Table2[Person]),Table2[Value] < [Median]))``
``Average Second HAlf = CALCULATE(AVERAGE(Table2[Value]), FILTER(ALLEXCEPT(Table2,Table2[Person]),Table2[Value] >= [Median]))``
``Divide Val = DIVIDE([Average First HAlf],[Average Second HAlf],0)``

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Helper III

Many thanks, it needed a bit of switching as the median is of the number time and the average is of the value. But once that is changed it works - thanks for your help, and I see what's going on now.

Announcements