Missing total hours with calculation

[Anonymous]

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 name should be in the overview. Right now I got a list with names and the gap between booked hours and contract hours but for some reason I don't get a total amount for that week.

I used the following measures:

Delta:

Delta = 
CALCULATE([Directe mdw. totaal (Geselecteerd)]-(SUM('DIM - Employee contracts'[Contracturen]));
    FILTER('DIM - Employees';'DIM - Employees'[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]))))

And this one:

Test = IF([Delta] <= -1;[Delta])

This results in a table:

Who can help to fix the measure(s) and get a total amount of hours? Many thanks

[v-juanli-msft · Accepted solution]

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]

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]

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!