Forum Discussion
Problem with granularity
Hi sergej_og
Thank you. You have explained perfectly. I will try, because Real Rate is not a table... It is calculated 😪 The calculus is a real headache
Maybe you can show a bit more of your data model or describe your case (especially the part with real rate) a bit more.
How do you get your real rates into your model?
Hi sergej_og
I'm gonna try to explain it:
- I have a dim table for centers with codes, location, full name...
- I have a fact table with all temporary medical leaves and absences due to holidays, marriages, or some any other reason
- I have a dim table with all the staff
- Finally, I have a calendar table
Then:
Sample of absences table would be:
Type of absence Subtype of absence Employee Code Employee ID Employee
Name
Starting date Ending date Center Key Absence Holidays 000001 98765431A Juan López 01/06/2023 07/06/2023 11A Medical leave Accident 000015 43275618Y David Fernández 08/06/2023 11A Absence Marriage 000002 12345679B MarÃa MartÃnez 04/06/2023 19/06/2023 11A Sample of Staff table will be:
Employee Code Full name Working Category Contract category Employee
% of hours
Starting date Ending date Center Key 000001 Juan López A Full time 100 07/05/2017 11A 000002 MarÃa MartÃnez B Temporary
75 01/06/2023 25/06/2023 11A 000015 David Fernández C Partial-time 89,74 01/05/2023 31/12/2023 11A So, with all this information what I'm doing to calculate the rate of people is:
- First, I compute if a person is on holidays or in a medical leave. Put a 0 if it is, or a 1 if not.
- Then, I compute if a person has an active contract. Put a 0 if it isn't or a 1 if it is active.
- I use both measures as a multiplier: employees rate * isOnHoliday* isActive, where employee rate is %hours/100
- Then, I summarized by the day
So, for instance Juan will have 0 for days between 1 and 7 of June, while for the rest of the month will give me a rate of 1. MarÃa will have 0,75 for days 1 to 3 and 20 to 25. 0 for all the rest. And so on...
Hope it is a little more clear now
mlsx4
ok, that´s a bit different starting point.
Can you post your calculations for computing "on holidays etc.", "is active etc.".
I will try to reconstruct and understand this.
Maybe a calculated table could be a possible solution.My measures are a bit mess, because I have tried several ways to do it. This way works for all the graphs I need to show (except the theoretical one), but they are not probably the optimum way of doing or even I'm spinning around in circles:
BajasD = CALCULATE(DISTINCTCOUNT('IT/ABS'[Employee name]), FILTER('IT/ABS','IT/ABS'[Starting date]<=MAX('Calendar'[Date]) && OR('IT/ABS'[Ending date] >=MAX('Calendar'[Date]),ISBLANK('IT/ABS'[Ending date]))))//The multiplier Multiplicador de bajas = IF([BajasD]=1,0,1)For active contract:
DiaT = CALCULATE( SUMX(VALUES('Calendar'[Date]),CALCULATE(DISTINCTCOUNT('Plantilla'[Employee code]), FILTER('Plantilla','Plantilla'[Starting date]<=MAX('Calendar'[Date])&& 'Plantilla'[Ending date]>=MAX('Calendar'[Date])))))//The multiplier Multiplicador de jornada = IF([DiaT]=1,1,0)Ratio jornada = VAR diasMes= DAY(LASTDATE('Calendar'[Date])) var num= SUMX(VALUES('Plantilla'[Employee Code]),CALCULATE(SUMX('Plantilla','Plantilla'[% Jornada]),FILTER('Plantilla','Plantilla'[Starting date]<=MAX('Calendar'[Date])))) RETURN num* CALCULATE([Multiplicador de bajas],USERELATIONSHIP(Employee code],'IT/ABS'[Employee code]))*[Multiplicador de jornada]Ratio totales = var summarizedTable = ADDCOLUMNS ( SUMMARIZE ( 'Plantilla', 'Plantilla'[Employee code],'Plantilla'[% Jornada]), "DÃas", CALCULATE ([Ratio jornada]) ) RETURN SUMX(summarizedTable,[Ratio jornada])