Forum Discussion
Issues with aggregating a measure.
We have a production floor report that essentially takes timeclock data and compares it to set standard minutes to generate efficiency ratings per employee.
The timeclock data is manually imported via SQL every morning as our timeclock system is 3rd party and they email us XLS files daily, there is no direct access to their database. Not only is this process not very reliable, but when employees miss punches (happens all the time) we have to manually go update the record to a default 8/10 hours (shift dependent) of time.
In efforts to get rid of this timeclock importing process, I have come up with a measure that basically just estimates the minutes at 480/600 shift respective. This works well when looking at the report for a given day since most employees are within 10-15 minutes of this time anyway and we dont have to deal with missed punches, but.... as soon as you try to view an aggregate of time like a month, the efficienices are all out of whack because its not summing the calculated minutes for that given time.
Here is my measure DAX: IF(MAX(Events[Date]) = TODAY(),SUMX(EmplTableAX, SWITCH(EmplTableAX[Shift], 1, MIN( 480, 24*60*(NOW() - (TODAY() + TIME(5,0,0)) )), 2, MIN( 600, 24*60*(NOW() - (TODAY() + TIME(13,45,0)))))),SUMX(EmplTableAX, SWITCH(EmplTableAX[Shift],1,480,2,600)))
Kind of tough to read, but essentially its figuring out the amount of minutes passed for the current day (so the efficiency value throughout the current day is valid), otherwise for all historical days its figuring out what shift the employee is (1st or 2nd) and setting the value to 480/600 respectively.
Any assistance is appreciated and let me know if you need more context or details. It can be tough to figure these specific issues out.
Progress. So using RELATED(EmplTableAX[Shift]) let it work in a calculated column, and it looks like its agreggating it properly...BUT it no longer works with the SP NET/GROSS measures since apparently one can't use a calculated column in a measure??
UPDATED: I got the SP NET/GROSS % measures to work by using SUM(Est Min), it will only work if its summing, not just the raw column reference. I think its all working now!
Thank you for your help Henry!
11 Replies
Could you please provide your table structure with some sample data and the expected output result? I’m not so clear about your ultimate goal with the measure provided.
Best Regards,
Herbert
Thanks for your reply. Please review below for details:
This first table has the shift. The second is the log of time worked. These two tables are related on PelitionID:
EmplTableAXEmployeeHoursWorked
The minutes are then divided by our standard minutes to create the efficiency %. And here is the end result issue I am facing:
- Using actual minutes from data provides 'correct' calcs:
- good
- Here it is with my measure that makes all minutes 480, which it does not seem to be calculating totals/avgs correctly:
bad
Where is “Est Mins” column from? And what is the formula of SP Gross %?
Do you mean the value which marked below is incorrect?
Best Regards,
Herbert
