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

 

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

    • davidpOuray's avatar
      davidpOuray
      Frequent Visitor
      9 years ago

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

        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