Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |