Forum Discussion

Flotenva's avatar
Flotenva
Frequent Visitor
9 years ago
Solved

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: Competi...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    9 years ago

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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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.

Flotenva's avatar
Flotenva
Frequent Visitor
9 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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.

     

    • Flotenva's avatar
      Flotenva
      Frequent Visitor
      9 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

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