Issues with aggregating a measure.

[davidpOuray]

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.

[v-haibl-msft]

@davidpOuray

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

[davidpOuray]

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 

[v-haibl-msft]

@davidpOuray

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

[davidpOuray]

Before I changed it, "Est Min" just pulled from totalminutes field unless it was the current day in which case it would calculate how many minutes have passed for the day when refreshed. I changed "Est min" to the DAX from my first post, essentially keeping the minute calculations for the current day otherwise just set it to 480/600 minutes, respective of shift. 

SP Gross % is just our standard minutes divided by "Est mins"

Your marked picture is correct. The total aggregates are not correct at all with this updated Est Mins logic.

[v-haibl-msft]

@davidpOuray

How about the result if we change the "Est Min" measure to column with following formula?

Est mins Column = 
IF (
    MAX ( Events[Date] ) = TODAY (),
    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 ) ) ) )
    ),
    SWITCH ( EmplTableAX[Shift], 1, 480, 2, 600 )
)

Best Regards,

Herbert

[davidpOuray]

Thanks Herbert!

I tried out  your formula and I get this:

"A single value for column 'Shift' in table 'EmplTableAX' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

This is likely why I had the SUMX in there (then it would only sum per row and there would be only 1 value of Shift then), but that obviously doesn't work correctly either. 

[v-haibl-msft]

@davidpOuray

According to the message you got, it seems that you used the formula to create a measure. Could you please try to create a calculated column with above “Est mins Column” expression?

Best Regards,

Herbert

[davidpOuray]

Creating a column with the same DAX gave the same error.

[v-haibl-msft]

@davidpOuray

Could you please share your .pbix file to me with OneDrive? I can help to troubleshoot it with the file.

Best Regards,

Herbert

[davidpOuray]

I actually am not currently using OneDrive (have never set it up or anything)... is there an alternate way to give it to you? It's around 20MB...

[davidpOuray · Accepted solution]

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!