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
I've been given the task of creating a "real time" rating dashboard of indidual and department workload per week.
Each employee has to rate their individual workload, after which I have to calculate the department's average workload. This part itself is pretty straightforward.
The problem is that the employees can continuously update their workload during the week if it changes. I therefore need the department's average workload to be calculated only on the basis of each person's most recent rating.
A simplified dataset could, for example, look like this:
Name | Rating | Date (dd-mm-yyyy) | Week |
Alan | 2 | 14-03-2023 | 11 |
Adam | 4 | 14-03-2023 | 11 |
Anna | 3 | 14-03-2023 | 11 |
Anna | 1 | 15-03-2023 | 11 |
Adam | 3 | 15-03-2023 | 11 |
Alan | 4 | 21-03-2023 | 12 |
Anna | 1 | 21-03-2023 | 12 |
Adam | 3 | 21-03-2023 | 12 |
Anna | 4 | 22-03-2023 | 12 |
For week 11 i need the average to be 2 based on every respondents latest rating (Alan 14-03-2023 = 2, Anna 15-03-2023 = 1, Adam 15-03-2023= 3), and not the average 2,6 based on all 5 values.
The same thing for week 12, where the average should be 3,6 based on the latest rating from each of the 3 respondents ratings and not 3 based on all 4 ratings.
How do i make this calculation?
I'm going to show the average value for the current week on one page, and on another page i will make a line-chart showing the development of the average rating for each week.
I'm using SQLBI's date template, so I do have a well populated date table.
Solved! Go to Solution.
Hi @ABech ,
Please try:
Average Workload =
AVERAGEX (
FILTER (
ALL ( 'Table' ),
[Week] = MAX ( 'Table'[Week] )
&& [Date (dd-mm-yyyy)]
= MAXX (
FILTER (
'Table',
[Week] = MAX ( 'Table'[Week] )
&& [Name] = EARLIER ( 'Table'[Name] )
),
[Date (dd-mm-yyyy)]
)
),
[Rating]
)
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @ABech ,
Please try:
Average Workload =
AVERAGEX (
FILTER (
ALL ( 'Table' ),
[Week] = MAX ( 'Table'[Week] )
&& [Date (dd-mm-yyyy)]
= MAXX (
FILTER (
'Table',
[Week] = MAX ( 'Table'[Week] )
&& [Name] = EARLIER ( 'Table'[Name] )
),
[Date (dd-mm-yyyy)]
)
),
[Rating]
)
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
102 | |
101 | |
78 | |
69 | |
63 |
User | Count |
---|---|
141 | |
106 | |
101 | |
85 | |
72 |