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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.