Aggr/Aggr not producing expected results (possible formatting issue?)

[t-gilchrist]

Hello all,

I have a data set of downtime instances that looks similar to this...

Order#     MaterialID     TotalBatchesInOrder     DowntimeDuration(hh:mm:ss)
2774         Tstock             23                                           00:06:52
2847         Tstock             25                                           00:08:27
2847         Tstock             25                                           00:05:16
2847         Tstock             25                                           00:03:56
2896         Tstock             23                                           00:06:27
2866         Astock            25                                           00:05:28

The Goal is to make an expression on the measure of a bar chart to sum the total downtime duration for a given MaterialID and divide it by the total batches ran for the corresponding MaterialID, excluding instances of a repeating Order# to get the downtime per batch as a measure with MaterialID as the dimension on the chart.

My current expression is...
(Aggr(Sum(DowntimeDuration),Material))/(Sum(Aggr(Max(TotalBatchesInOrder),Material,Order#)))

Expression Testing
- When I use only Aggr(Sum(DowntimeDuration),Material) and filtered to "Tstock" I get my desired result of 00:30:58 when I format my bar chart measure as duration.
- When I use only Sum(Aggr(Max(TotalBatchesInOrder),Material,Order#)) and filtered to "Tstock" I get my desired result of 71 batches when i format my bar chart measure as a number.

Results
But when I use the full expression I do not get my expect output of 0.4362 (30.9666/71) instead...
- When the chart is formatted as a number I get 00.00030
- When the chart is formatted as a duration I get 00:00:26

Any help in figuring out why I am not getting my expected output or alternate ways to achieve my goal would be appreciated 

[t-gilchrist]

Its becoming much more clear, I apologize for all the questions.

So my original thinking was if my result was correct as a number and matched my calculator using (30+(58/60)/71=30.96666 then that would be a verification that when i switch it back to duration that the number was correct.
But that was incorrect because 30.96666 is not the numerical representation of 00:30:58. In actuality I was dividing 0.02150/71 which is why I was getting a result of 0.0003 as a number.

So that would mean if I want to use duration as my final format then the original formula without *24*60 would be correct?

[sunny_talwar]

Absolutely bang on target 🙂