Calculation (average) of user ratings using only the latest rating from every respondent

[ABech]

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.

[v-jianboli-msft · Accepted 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.

[ABech]

Hi @v-jianboli-msft 

Thank you so much. That seems to work to work perfect!