cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IHam
Helper III
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.
Thanks in advance.
Here is a small sample of the data: 
 
ValuePersonNumber Time
611
9121
5831
2541
5871
3512
3422
1232
8142
662
6133
8343
7553
1763
973
1 ACCEPTED SOLUTION
harshnathani
Super User
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)

 

1.JPG

 

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

View solution in original post

2 REPLIES 2
harshnathani
Super User
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)

 

1.JPG

 

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

View solution in original post

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.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors