Forum Discussion
Problem with granularity
Hello everyone! I have a problem that I cannot figure out how to solve. Let's say I need to compute the difference between my theoretical rate and my real rate. Therefore I have a table for my ...
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])