Forum Discussion

kdunaev's avatar
kdunaev
Regular Visitor
6 years ago

Help with aggregation

Hello! I am new on here - first post!!

I have a measure calculated as: Missed Meal Hrs = count('Final Data'[Employee ID])*.5

I am trying to create another measure that would take a min value of the above measure and another value:

Missed Meal OT = min(sum('my data'[Overtime Hours]),[Missed Meal Hrs])

the structure of my data is such that for each [Employee ID] I have multiple rows. then all Employee IDs can be grouped into areas. When I look at my data as a table where each row is an Employee ID - the 2 formulas above calculate correctly. But when I take Employee IDs out of the table, and each row is now an Area - the Missed Meal OT formula does not aggregate at the Employee ID level, and tries to just use the totals of each area, which is not what I want. Any help would be greatly appreciated!

5 Replies

      • kdunaev's avatar
        kdunaev
        Regular Visitor

         

        Division

        Employee IDMissed Meal HrsOT HoursMissed Meal OT hours
        AA123452.500
        AA123462.500
        AA123472.50.220.22
        AA123482.59.922.5
        AA123492.52.182.18
        AA123502.50.050.05
        BB123512.52.932.5
        BB123522.500
        BB123532.50.60.6
        BB123542.53.752.5
        BB123552.54.822.5
        BB123562.50.150.15

         

         

        Desired outputMissed Meal HrsOT HoursMissed Meal OT hours
        AA total15.0012.374.95
        BB Total15.0012.258.25

         

         

        What I am getting:Missed Meal HrsOT HoursMissed Meal OT hours
        AA total15.0012.3712.37
        BB Total15.0012.2512.25