Getting an average where columns match

[JGroothedde]

Hi there friends,

I have data like below:

IDPerson	Job type	Job option	Job category	Hourly rate	Average rate
134	Type 1	Option 1	On-site	60	58,33
135	Type 2	Option 1	On-site	55	58,33
136	Type 1	Option 1	On-site	75	58,33
137	Type 4	Option 4	Remote	40	58,33
138	Type 1	Option 3	Remote	60	58,33
139	Type 5	Option 1	On-site	60	58,33

What would be the best way, in DAX, to get the average based on matching columns? So instead of a general average of the hourly rate calculated over all rows, i need the average to be calculated based on matching columns (aside from the person ID). So the average in the first row should be calculated for rows where job type = Type 1, job option = Option 1, category = On-site.

What's the best way to accomplish this? I'm stumped.

Cheers in advance!

[rajendraongole1 · Accepted solution]

Hi @JGroothedde - create a below calculated column that dynamically calculates the average for each row based on the specified conditions

calculated column:

Average rate =

CALCULATE(

    AVERAGE('Avger'[Hourly rate]),

    ALLEXCEPT('Avger', 'Avger'[Job type], 'Avger'[Job option], 'Avger'[Job category])

)

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[JGroothedde]

Thank you so much for getting me out of this, for some reason I kept trying to do this in a measure. Thanks!