Forum Discussion

davidpOuray's avatar
davidpOuray
Frequent Visitor
9 years ago
Solved

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 importe...
Need Help
  • davidpOuray's avatar
    davidpOuray
    9 years ago

    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!

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
9 years ago

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's avatar
davidpOuray
Frequent Visitor
9 years ago

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's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    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's avatar
      davidpOuray
      Frequent Visitor
      9 years ago

      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's avatar
        v-haibl-msft
        Microsoft Employee
        9 years ago

        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