Finding the averages of different halves of a data set

[IHam]

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.

Thanks in advance.

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

[harshnathani · Accepted solution]

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)

[IHam]

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.