Forum Discussion
Wrong Time Calculation
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!
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.
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
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.
8 Replies
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.
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
Works perfect, thank!!
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.
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?
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.
