cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Flotenva Frequent Visitor
Frequent Visitor

Average selected data (Athletes' performances)

Hi there,

I have a list of athletes, events and performances and I would like to average the performances of each athlete in a specific event. The following is an example of the dataset:

Competition_Results_Family_nameCompetition_Results_CountryCompetition_Results_ScoreCompetition_Results_PhaseCompetition_Results_Event
ABELCAN323.25FinalWomen 10m Springboard
ABELCAN347.15FinalWomen 10m Springboard
ABELCAN338.35FinalWomen 10m Springboard
ABELCAN316.95FinalWomen 3m Springboard
ABELCAN286.5FinalWomen 3m Springboard
AUFFRETFRA486.3FinalWomen 10m Springboard
AUFFRETFRA491.25FinalWomen 10m Springboard
AUFFRETFRA493.4FinalWomen 10m Springboard

 

For example, I would like to average athlete's performances in Women 10m Springboard only. In the case of ABEL (Average[323.25, 347.15, 338.35]) and AUFFRET (Average[486.3, 491.25, 493.4]) and do the same with all the athletes participating in that specific event... Is there a way (DAX) to do this?

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Ross73312 Super Contributor
Super Contributor

Re: Average selected data (Athletes' performances)

Hi @Flotenva, looks like you are missing the ALL line from my code example.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

6 REPLIES 6
Ross73312 Super Contributor
Super Contributor

Re: Average selected data (Athletes' performances)

Short answer is yes.  How you do it will depend on your overall approach.

 

A simple example would be a Dax measure that is simply "=Average(Table[Field])"  (where Table and Field is the field in your table you wish to average).  Place that formula on a card and add in 2 slicers.  1 slicer to pick the event and the other to pick the athlete.

 

 

If you wanted to get DAX to do all of it, you would use CALCULATE and you could set that in the formula.  You would need to figure out how DAX will learn of your selection, but if you give us some more details i'm sure we could advise.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Flotenva Frequent Visitor
Frequent Visitor

Re: Average selected data (Athletes' performances)

Thanks Ross,

Calculating the average is just an example...

My main concern is to know if it is possible to use a DAX to do calculations on a specific dataset when values in 2 columns are the same. For example, doing average on a dataset when value in COLUMN1 and COLUMN2 coincide.

NAME       EVENT       PERFORMANCE    AVERAGE

Athlete A - Event A - 10.0

Athlete A - Event A - 10.5

Athlete A - Event A - 11

Athlete A - Event B - 0

Athlete A - Event B - 0.5

Athlete A - Event B - 1

 

In this case, I would like to be able to calculate the average for Athlete A in Event A [10.0, 10.5, 11] = 10.5 and

calculate average for Athlete A in event B [0, 0.5, 1] = 0.5

But I said, average is just an example... in the future I would like to Normalize the performance Score according to previous performances..

Ross73312 Super Contributor
Super Contributor

Re: Average selected data (Athletes' performances)

I think i get what you are after.  You would need to use the "Create Column" and try something like this:

AveragePerformance = Calculate(
	Average('Table'[Performance]),
	all('Table')
	'Table'[Event] = EARLIER('Table'[Event]),
	'Table'[Name] = EARLIER('Table'[Name])
)

I hope that might create what you are chasing.

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Flotenva Frequent Visitor
Frequent Visitor

Re: Average selected data (Athletes' performances)

Hi Ross,

I have done what you suggested:

 

Rank = CALCULATE(

AVERAGE('Athletics (ParseHub)'[Mark]),

'Athletics (ParseHub)'[Name]=EARLIER('Athletics (ParseHub)'[Name]),

'Athletics (ParseHub)'[Event]=EARLIER('Athletics (ParseHub)'[Event])

)

 

But I'm getting exactly the same value as in the column [Mark]...

Ross73312 Super Contributor
Super Contributor

Re: Average selected data (Athletes' performances)

Hi @Flotenva, looks like you are missing the ALL line from my code example.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

Flotenva Frequent Visitor
Frequent Visitor

Re: Average selected data (Athletes' performances)

Thank you so much!!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,122)