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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

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!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors