Calculate Total of Time Not Working

[kattlees]

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

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

[v-xjiin-msft · Accepted solution]

Hi @kattlees,

Simply a new measure:

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

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
)

Thanks,
Xi Jin.

[kattlees]

THANK YOU!!!!