cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver II

Getting an average where columns match

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.

1 ACCEPTED SOLUTION
Super User

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!

Proud to be a Super User!

2 REPLIES 2
Super User

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!

Proud to be a Super User!

Resolver II

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.