Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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.
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
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.
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
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
This works! Again, thank you so much for your time and assistance - you really helped me out here! 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |