Wrong Time Calculation

[timbckr812]

Good Morning,

I have an issue with time calculation in Power BI. In my case, I’ve determined the production lead time for multiple orders and stored it in a table column in the time format "hh:mm:ss." Each of the orders has a different quantity, so my next step is to calculate the production time per unit.

My approach for this is:

Time per Unit =  
SUM('Production Orders'[Production Time])/SUM('Production Orders'[Quantity])

However, the result is incorrect. I’ve also tried saving the lead time in decimal format, but the result remains the same. The solution should be 00:00:55, but the result I am getting is 19:52:07.

Perhaps someone has an idea of where my mistake might be.

A sample file is attached.

Order	Quantity	Production Time
1	2	00:03:10
2	3	00:02:05
3	4	00:04:52
4	2	00:01:44
5	4	00:02:36
6	2	00:02:16

Thank you!

[rajendraongole1 · Accepted solution]

Hi @timbckr812  - First, convert the production time to a numerical value, i.e., seconds, to ensure proper summing.

create a measure as follows;
Production Time per Unit =
VAR TotalTimeSeconds = SUMX('Production Orders',
(HOUR('Production Orders'[Production Time]) * 3600) +
(MINUTE('Production Orders'[Production Time]) * 60) +
SECOND('Production Orders'[Production Time]))
VAR TotalQuantity = SUM('Production Orders'[Quantity])
VAR TimePerUnitSeconds = TotalTimeSeconds / TotalQuantity
RETURN
FORMAT(TIME(0,0,TimePerUnitSeconds), "hh:mm:ss")

It sum the total production time in seconds and divide by the total quantity. please check the above and let know.

[divyed · Accepted solution]

Hello @timbckr812 ,

You can try below dax :

Time per Unit =
VAR TotalMinutes = SUMX(
'Production Orders',
HOUR('Production Orders'[Production Time]) * 60 +
MINUTE('Production Orders'[Production Time]) +
SECOND('Production Orders'[Production Time]) / 60
)
VAR TotalQuantity = SUM('Production Orders'[Quantity])
VAR MinutesPerUnit = TotalMinutes / TotalQuantity
RETURN
TIME(INT(MinutesPerUnit / 60), INT(MOD(MinutesPerUnit, 60)), 0)

Cheers

[danextian · Accepted solution]

Hi @timbckr812 

I can't replicate your expected result using your sample data even if the time per unit is evaluated separately for each order and then summed up in the total row but I am definitely not getting  19:52:07

Attached is the sample pbix used in the screenshot.

[timbckr812]

First of all thanks for your reply 🙂

This is my solution. As soon as I format the time as a decimal number, I get results that differ from yours. There's always a "1" before the decimal point—could that be the problem? How do i fix that?

[danextian]

Is an order equivalent to a single record in your data or there are multiple records involved?  If there are multiple records which is not in your descrption, that's the only reason I can think of why your sum is overstated.

[timbckr812]

Yes ist equvalent to a single record.  I import the files from an Excel file, where the production time is stored in time format. As soon as I load the data into Power BI and then convert it into a decimal number, I get the "1" before the decimal point. Please see the attached images.

[timbckr812]

I got it now, the import type was datetime and not time, as seen in the picture. My bad thank you!!

[divyed · Accepted solution]

Hello @timbckr812 ,

You can try below dax :

Time per Unit =
VAR TotalMinutes = SUMX(
'Production Orders',
HOUR('Production Orders'[Production Time]) * 60 +
MINUTE('Production Orders'[Production Time]) +
SECOND('Production Orders'[Production Time]) / 60
)
VAR TotalQuantity = SUM('Production Orders'[Quantity])
VAR MinutesPerUnit = TotalMinutes / TotalQuantity
RETURN
TIME(INT(MinutesPerUnit / 60), INT(MOD(MinutesPerUnit, 60)), 0)

Cheers

[timbckr812]

Works perfect, thank!!

[rajendraongole1 · Accepted solution]

Hi @timbckr812  - First, convert the production time to a numerical value, i.e., seconds, to ensure proper summing.

create a measure as follows;
Production Time per Unit =
VAR TotalTimeSeconds = SUMX('Production Orders',
(HOUR('Production Orders'[Production Time]) * 3600) +
(MINUTE('Production Orders'[Production Time]) * 60) +
SECOND('Production Orders'[Production Time]))
VAR TotalQuantity = SUM('Production Orders'[Quantity])
VAR TimePerUnitSeconds = TotalTimeSeconds / TotalQuantity
RETURN
FORMAT(TIME(0,0,TimePerUnitSeconds), "hh:mm:ss")

It sum the total production time in seconds and divide by the total quantity. please check the above and let know.