Pivot Table with Sum Sickness Hours by Division and Period (2 data sources in modelling / Connected with PeriodEmployeeID

[Michael15]

Dear all, 

I have a simple data model with one source like this:

Employee ID Period	Period	Sickness Hours
300 2024-01	2024-01	8
100 2024-02	2024-02	3
100 2023-12	2023-12	2
200 2023-12	2023-12	5
400 2024-02	2024-02	5

The other data source in data modelling is masterdata with same connector Employee ID Period and the Division assigned:

Employee ID Period	Division	Period
100 2024-02	AUT	2024-02
200 2023-12	MED	2023-12
300 2024-01	ELE	2024-01
100 2024-01	AUT	2024-01
400 2024-02	AUT	2024-02

I would like to have a pivot table out of these two sources like this.

Sum of Sickness Hours by Division and Period.

Division	2023-12	2024-01	2024-02
AUT	2	0	8
MED	5	0	0
ELE	0	8	0

Which measures do I need to create to achieve that? Thank you so much for your help.

[LRuCelver]

I believe the 4th row in your second table is supposed to be for period 2023-13.

When using Sum("Sickness Hours") as the measure I get this table:

[Michael15]

You are fully right with the 4th row.

Unfortunately, When I use Sum("Sickness Hours") in my example I have all the same total result of sickness hours for each division (it just displayes the sum of sickness hours for all, instead for just the Division assigned) any idea to solve that?

[LRuCelver]

Can you share a screenshot of your data model?

[Michael15]

Here you go:

[LRuCelver]

It looks like the sickness hours are linked correctly to the employee and division. But the YearMonth field you used is either not connected to the others or is connected to all values.

Please check your keys and verify that they are correct. Check all joins in your script. If the keys don't match you might have accidentally assigned a key to ALL values.