cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Frequent Visitor

## Calculate over and under booking of hours (COUNTIF, SUMIF)

Hello guys,

This is my data

1. I want to count how many times, Total_Hours_Actual are more than 8, =8, and less than 8.

2. Then I want to calculate the values for the same in terms of hours.

I want this calculation to be done only for columns Date and workcenter (avoid all other columns). So, I wrote a Measure with allselected to avoid effect of ACTIVITY_TYPE( and some other columns). It worked as you can see my "Measure" named column.

Then, Measure 2 is checking if (measure>8,1,0).

I am stuck at this point. The aggregation/total of measure 2 is saying 1 which is obvious. I want to sum all the 1s.

Please suggest an approach. I am using MicroStrategy's approach but maybe there is other way to reach the result.

Thanks a lot

2 REPLIES 2
Super User IX

## Re: Calculate over and under booking of hours (COUNTIF, SUMIF)

Measure = calculate(sum(Table[Total_Hours_Actual]),Table[Total_Hours_Actual ] >=8)

Measure 2= calculate(sum(Table[Total_Hours_Actual]),Table[Total_Hours_Actual ] <8)

to show in term of hours refer

https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...

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
Frequent Visitor

## Re: Calculate over and under booking of hours (COUNTIF, SUMIF)

Sorry that won't work.

I want to count per day per employee

1. Number of Times >8, = 8, <8

2. Total value of Over booking (actual hours - 😎 and under booking (8-actual hours)

## Helpful resources

Announcements

#### Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors