cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kattlees
Post Patron
Post Patron

Calculate Total of Time Not Working

I have a table of times people are in an OR Room. Each row has a Start Time and End Time.

Example:

OR 1    07:05     09:15
OR 1    09:45     11:15

OR 2    07:00     07:30

OR 2    08:00     10:00

OR 3    12:15     14:30

 

I have a measure to get First Start Time of the day per or and Last End Time per day per or and to get the total time for that. So for the example above it would be

OR     First Start Time      Last End Time     Total OR Time

OR 1     07:05                        11:15                     4.17  (250 minutes)

OR 2     07:00                        10:00                     3.00   (180 minutes)

OR 3     12:15                        14:30                     2.25    (135 minutes)

What I need is a table that looks like this but TOTALS the Total OR Time.  In the image below, the left side amounts are correct, but when I choose (SUM) under the values, it changes everything to what you see in the middle.  The one on the right is what I want it to look like

ScreenShot.jpg
Here are my formulas:

 

 

First Case By OR =

CALCULATE (
MIN('Leslie OR Times'[Guest In Block] ),
ALLEXCEPT ( 'Leslie OR Times', 'Leslie OR Times'[SCHDT],'Leslie OR Times'[OR] )
)

 

Last Case By OR =

CALCULATE (
MAX('Leslie OR Times'[Guest Out Block] ),
ALLEXCEPT ( 'Leslie OR Times', 'Leslie OR Times'[SCHDT],'Leslie OR Times'[OR] )
)

Total OR Time by OR = DATEDIFF('Leslie OR Times'[First Case By OR] ,'Leslie OR Times'[Last Case By OR],MINUTE)/60

1 ACCEPTED SOLUTION
v-xjiin-msft
Solution Sage
Solution Sage

Hi @kattlees,

 

Simply a new measure:

 

SUM Total =
SUMX ( VALUES ( 'Leslie OR Times'[OR] ), [Total OR Time by OR] )

5.PNG

 

Or all the calculations can be included in one calculated table:

 

New Calculate Table =
SUMMARIZE (
    'Leslie OR Times',
    'Leslie OR Times'[SCHDT],
    'Leslie OR Times'[OR],
    "First Case By OR", MIN ( 'Leslie OR Times'[Guest In Block] ),
    "Last Case By OR", MAX ( 'Leslie OR Times'[Guest Out Block] ),
    "Total OR Time By OR", DATEDIFF (
        MIN ( 'Leslie OR Times'[Guest In Block] ),
        MAX ( 'Leslie OR Times'[Guest Out Block] ),
        MINUTE
    )
        / 60
)

6.PNG

 

Thanks,
Xi Jin.

View solution in original post

2 REPLIES 2
v-xjiin-msft
Solution Sage
Solution Sage

Hi @kattlees,

 

Simply a new measure:

 

SUM Total =
SUMX ( VALUES ( 'Leslie OR Times'[OR] ), [Total OR Time by OR] )

5.PNG

 

Or all the calculations can be included in one calculated table:

 

New Calculate Table =
SUMMARIZE (
    'Leslie OR Times',
    'Leslie OR Times'[SCHDT],
    'Leslie OR Times'[OR],
    "First Case By OR", MIN ( 'Leslie OR Times'[Guest In Block] ),
    "Last Case By OR", MAX ( 'Leslie OR Times'[Guest Out Block] ),
    "Total OR Time By OR", DATEDIFF (
        MIN ( 'Leslie OR Times'[Guest In Block] ),
        MAX ( 'Leslie OR Times'[Guest Out Block] ),
        MINUTE
    )
        / 60
)

6.PNG

 

Thanks,
Xi Jin.

View solution in original post

THANK YOU!!!!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

Top Solution Authors