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.
Hi,
I`m looking for some help with some measures that dont seem to be lining up.
I`m trying to calculate the amount of days absence employees have taken in a given period of time per department. I have three tables to base the measures on. The end result I`m after is the amount of days absence divided by the rolling average FTE for the last 12 months.
Table 1: Absences - this shows a list with one line per absence. Each line contains an employee number, type of absence, department, and hours absent
Table 2: Resource reports - these are a snapshot of all departments taken on first day of each month. Its contains a list of all employees and thier FTE (full time equivalent). I have added a measure to this that show the sum of FTE so i know how many full time equavalent employees there are in each period in each department.
Table 3: Rolling calendar
Measure I have so far:
Sum of FTE
12 month rolling average for FTE (FTE for 12 months /12)
Sum of hours: Sum of hours absence per month
Days absent: Sum of hours /7.5 (7.5 equates to one full day)
Days absent PP: This is the Days Absent / 12 month rolling FTE
All these work well in a table with no slicers - when I add costc(T) slicer (department) it only works if i have two slicers (one for absence table and one for resource table) both for Costc(T). For example i only get the correct result when i select HR in each slicer.
Any help would be appreciated. Please see link below to .pbix
Thanks,
Brendan
Solved! Go to Solution.
You need to learn the rules of proper dimensional modelling. Check out the attached file.
Best
D
Hi darlove,
Many thanks - This has been replicated in my real model. Thanks for the tip on Dimensional Modelling. I`m really new to Power BI and data modelling in general so any advice is much appreciated.
Thanks,
Brendan
Thanks darlove,
I`ll definately watch the video and look more into the "Star Schema".
Thanks,
Brendan
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |