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 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

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.

3 Replies

  • 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!

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    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.