Calculate average for the category of a filtered item

[aekoll]

Hello everyone,

I am relatively new to Power BI and am currently haven trouble to get my head around a certain problem with DAX in Power BI and would really appreciate some ideas.

I have two tables that are linked with each other. The first one has all workers of a company and looks like this:

ID	User Name	Role
123456	Peter Thompson	HR
123457	Julia Poe	Backend Developer

The second tabe lists all time entries that each person has booked on any project over all time. This is simplyfied but should be enough for my problem

user id	project	time entry lenght in min	date
123456	XYZ	60	12.12.2021

On my report page I have a line chart that shows how much time was booked in % on a specific project over time. Additionally I have a filter for the user, which allows me to show these values for each user. To be able to make a comparision, I calculated the average over all user with a Caluclate ("", ALL()) function so that it's not affected by the filter itself. The chart filtered for "Julia" looks like this:

So far so good. What I want to add to this chart is another measure that calculates the average value of the Role of the selected user. So if I filter the report for "Julia Poe", I want this bar chart to also show the average of all users that do have the same role "Backend Developer" as she does.

I do not really have a good idea on how to solve this issue and was so far not able to find a similar request. If so, I am truly sorry!

Let me know if anything is unclear. Thank you so much for your help!

Best,
Andreas

Edit: to give some further expanation: I want a measure that looks more or less like this:

team billable minutes = CALCULATE(SUM(time(time entry in minutes), FILTER(time, RELATED(Users[role]) = "Developers Backend")) , just the last part "developers backend" should be dynamic and be defined by the role that the currently filtered user has.

[v-kkf-msft · Accepted solution]

Hi @aekoll ,

Please try the measure.

Measure = 
CALCULATE (
    SUM ( 'time'[time entry lenght in min] ),
    FILTER ( ALL ( Users ), Users[Role] in VALUES ( Users[Role] ) )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-kkf-msft · Accepted solution]

Hi @aekoll ,

Please try the measure.

Measure = 
CALCULATE (
    SUM ( 'time'[time entry lenght in min] ),
    FILTER ( ALL ( Users ), Users[Role] in VALUES ( Users[Role] ) )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aekoll]

Hi Winniz

Thank you so much for your help. Your solution does exactly what I expected it to do. The only thing that is not optimal (but if there is no way around, so be it), is that the measure does not calculate the values for the entire time but only for the time the user has entries. To show you what I mean I posted a screenshot below:

Yellow is the measure for the entire data, blue for the filtered person and orange is your measure. Now of course I have data for that team that goes further back, it's just that the person that is filtered only started to have time entries in 2017. Do you have a solution for that?

Thank you again for your assistance and please let me know if anything is unclear.

Best,
Andreas

[v-kkf-msft · Accepted solution]

Hi @aekoll ,

Please try this measure to see if it returns the correct value.

Measure = 
CALCULATE (
    SUM ( 'time'[time entry lenght in min] ),
    FILTER ( ALL ( Users ), Users[Role] in VALUES ( Users[Role] ) ),
    ALL (),
    VALUES ( Dates[date] )
)

Best Regards,
Winniz

[aekoll]

This works! Again, thank you so much for your time and assistance - you really helped me out here! 🙂