Calculating a weighted average with multiple conditions (on other tables)

[Oberon]

I’m looking to calculate the weighted average of employee occupancy for a given week for employees in a specific team. (Weighted by login time).

I’ve tried a few methods but can’t seem to pull it together. I can determine what team employees are in on a given date but struggle to then use that to reference their login time and occupancy. I don’t mind if the method uses measures, calculated columns, transforming data, creative data table linking and slicers or any combination of methods, happy to try anything.

The following tables represent the ones I'm using: (Along with a date table)

Employee ID	Week Beginning	Occupancy	Login Time (Seconds)
111	05/5/25	100%	1000
123	05/5/25	50%	850
143	05/5/25	33%	2000
432	05/5/25	88%	75
657	05/5/25	72%	800
345	05/5/25	75%	950
333	05/5/25	65%	1500
111	12/5/25	83%	1200
123	12/5/25	77%	1850
143	12/5/25	60%	900
432	12/5/25	82%	775
657	12/5/25	90%	600
345	12/5/25	75%	1250
333	12/5/25	55%	1200

Employee ID	Team	Start Date	End Date
111	Business	01/01/24
123	Business	01/01/24
143	Consumer	01/01/24	11/05/25
432	Consumer	01/01/24	03/02/25
657	Consumer	01/01/24
345	Business	01/01/24
345	Business	01/01/24
143	Business	12/05/25
432	Business	04/02/25

The output I’m looking for would be something along the lines of:

Week Beginning	Team	Occupancy
5/05/2025	Business	72.65%
5/05/2025	Consumer	44.14%
12/05/2025	Business	72.38%
12/05/2025	Consumer	90.00%

[v-tsaipranay · Accepted solution]

Hi @Oberon ,
Thank you for reaching out to the Microsoft Fabric Community Forum. Also thankyou @danextian , @pankajnamekar25  and @Irwan  for your valuable input on this thread.

After thoroughly reviewing your scenario and the sample data provided, I was able to reproduce the issue and implement a working solution on my end. I’ve modeled the data accordingly and calculated the weighted average occupancy per week by team, based on login time.

To help you better understand the implementation, I’ve included a .pbix file with the full working solution for your reference, please take a look and feel free to explore how the relationships, measures, and visuals are set up.

If this post helps, please give it a Kudos and consider marking it as Accepted Solution  this will help other community members find it more easily.

Thank you.

[Oberon]

Thank you @v-tsaipranay that works nicely.

I had to do a bit more fiddling to get it to work with my main pbix but the sample got me there. The key piece seemed to be he inclusion of the calculated table with GENERATE.

Much obliged.

[danextian]

Hi @Oberon
How did you determine the value of 72.65% for row 1 in your sample output? What logic or calculation did you use? Additionally, if employee 432’s end date is 3/2/25, why is there still a record for them in table 1 that goes beyond that end date?

[Oberon]

Hi @danextian,

The 2nd table has more columns than shown above and is primarily used to evaluate the number of employees on a given date for a given position.  Each new row is an employee movement, be it starting with the company, leaving the company, or switching roles (or teams in this case). It's the primary data table for recording employee changes over time.  I included employee 432 in the table is that's representative of how my actual data looks, so a solution wasn't provided which wouldn't take it into account.

The calculation for business occupancy on the 05/05/25 would be first evaluating all the employees that are in the business team on the week beginning 05/05/25. Then sum the occupancy * login time for each employee row that meets the "Business team on 05/05/25" criteria. Once all that is summed up, divide it by the total sum of the login time for all the employees that were in the business team on the week beginning 05/05/25. That should land you at 72.65%. (The calculations probably easier to test on the consumer team as there's only 2 employees in the team in the 1st week and 1 in the second) .

[pankajnamekar25]

Hello @Oberon 

Use this measure

Weighted Avg Occupancy (%) :=

VAR SelectedWeek = SELECTEDVALUE('EmployeeMetrics'[Week Beginning])

RETURN

    CALCULATE(

        DIVIDE(

            SUMX(

                FILTER(

                    'EmployeeMetrics',

                    VAR EmpID = 'EmployeeMetrics'[Employee ID]

                    RETURN

                        CALCULATE(

                            MAX('EmployeeMetrics'[Occupancy]) * MAX('EmployeeMetrics'[Login Time]),

                            FILTER(

                                'EmployeeTeamMapping',

                                'EmployeeTeamMapping'[Employee ID] = EmpID

                                && 'EmployeeTeamMapping'[Start Date] <= SelectedWeek

                                && (

                                    ISBLANK('EmployeeTeamMapping'[End Date])

                                    || 'EmployeeTeamMapping'[End Date] >= SelectedWeek

                                )

                                && 'EmployeeTeamMapping'[Team] = SELECTEDVALUE('EmployeeTeamMapping'[Team])

                            )

                        )

                ),

                [Value]

            ),

            CALCULATE(

                SUMX(

                    FILTER(

                        'EmployeeMetrics',

                        VAR EmpID = 'EmployeeMetrics'[Employee ID]

                        RETURN

                            CALCULATE(

                                MAX('EmployeeMetrics'[Login Time]),

                                FILTER(

                                    'EmployeeTeamMapping',

                                    'EmployeeTeamMapping'[Employee ID] = EmpID

                                    && 'EmployeeTeamMapping'[Start Date] <= SelectedWeek

                                    && (

                                        ISBLANK('EmployeeTeamMapping'[End Date])

                                        || 'EmployeeTeamMapping'[End Date] >= SelectedWeek

                                    )

                                    && 'EmployeeTeamMapping'[Team] = SELECTEDVALUE('EmployeeTeamMapping'[Team])

                                )

                            )

                    ),

                    [Value]

                )

            )

        ) * 1.0

    )

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[Oberon]

Hi Pankajnamekar,

Could you please clarify what [Value] is referrring to in the code? I realise I didn't provide table names so not 100% what it's referencing. I can marry up the rest okay.

Thank you 🙂

[Irwan]

hello @Oberon 

the quickest way is using relationship then table visual.

however, i dont see about the usage of start and end date in table 2.

i might be wrong but i dont see in the description above.

otherwise, please describe more about the use of table 2.

Also is occupancy and login time are measures?

Thank you.

[Oberon]

Hi Irwan,

Ocupancy and login time are table columns.

Table 2 indicates which team employees are in on any given date. So using employee ID 143 as an example he's in the Consumer team for the week of 05/05/25 and then moves into the Business Team for for the week of 12/05/25. So in the first week of the example I'd want his Occupancy counting against the Consumer team and the Business team for the second week.