cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

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 5
Highlighted
Super User IV
Super User IV

@kdunaev ,Can you share sample data and sample output.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

For some reason, I'm having a hard time with copying a table into the body of the comment and don't see how to attach a file. Hopefully this link works:

 

https://www.dropbox.com/s/atwxz9wezhz1tna/Book1.xlsx?dl=0

 

Highlighted

 

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
Highlighted

@kdunaev , I think somehow I am not getting what you were getting

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-accessibility-keyboard-shortcuts



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

Hi @kdunaev ,

 

You can use the below measures.

 

Missed Meal OT Hr = CALCULATE(SUM(Table5[Missed Meal OT hours]), ALLEXCEPT(Table5,Table5[Division]))
 
Missed Meal = CALCULATE(SUM(Table5[Missed Meal Hrs]), ALLEXCEPT(Table5,Table5[Division]))
 
OT Hourss = CALCULATE(SUM(Table5[OT Hours]), ALLEXCEPT(Table5,Table5[Division]))
2.JPG
 
 
Regrards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors