Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Missing total hours with calculation

Hi everyone,   I'm trying to create a weekly overview of employees who work/book more than 1 hours less than they should. For example: my contract is for 40 hours a week, if I book only 38 hours my...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    Which table and column does the measure [Directe mdw. totaal (Geselecteerd)] use?

     

    If it uses columns in "entries" table, 

    i create measures as below, it can works on my side.

     

    Please check if it fits your scenario.

    Measure1 =
CALCULATE (
    SUM ( contacts[con] ),
    FILTER (
        contacts,
        contacts[direct/indirect] = TRUE ()
            && contacts[Is active] = TRUE ()
            && contacts[effective date] <= MAX ( 'calendar'[Date] )
            && contacts[expiration date] >= MIN ( 'calendar'[Date] )
    )
)

measure2 = SUM(entries[value])

Measure3 =
CALCULATE (
    [measure2] - SUM ( contacts[con] ),
    FILTER (
        contacts,
        contacts[direct/indirect] = TRUE ()
            && contacts[Is active] = TRUE ()
            && contacts[effective date] <= MAX ( 'calendar'[Date] )
            && contacts[expiration date] >= MIN ( 'calendar'[Date] )
    )
)

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous 

How about adding [Delta] in the table?

If [Delta] shows correctly, use this measure

Measure = IF(HASONEVALUE(Table1[Naam]),IF([Delta]<-1,[Delta]),[Delta])

If it doesn't show total as well,

please show me the relationships among these tables used in your formula,

also tell me which table the "Naam" column comes from.

 

Best Regards
Maggie

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi v-juanli-msft

 

Thank you for your answer. Your measure shows the total, but not the expected total:

 

It shows 42,47 but if I sum the values I expect a total of -42,99. Right now it sums all the values, including the hidden values. Can you help me with that?

 

Used formula:

 

Delta = 
VAR _delta = CALCULATE([Directe mdw. totaal (Geselecteerd)] - (SUM('DIM - Employee contracts'[Contracturen]));
    FILTER('DIM - Employee contracts';'DIM - Employee contracts'[Is actief] = TRUE);
    FILTER('DIM - Employee contracts';'DIM - Employee contracts'[Direct/indirect] = TRUE);
    FILTER('DIM - Employee contracts';'DIM - Employee contracts'[Effective date] <= CALCULATE(MAX('DIM - Calendar'[Datum])) && 'DIM - Employee contracts'[Expiration date] >= CALCULATE(MIN('DIM - Calendar'[Datum]))))

RETURN
    IF(HASONEVALUE('DIM - Employees'[Naam]);IF(_delta <= -1;_delta);_delta)

 

Relationships among the tables:

 

Naam comes from DIM - Employees.

 

Thank you!