Issue with rows not summing to the correct number! Help need for work!

[chryslerDAWGs]

I have been stumped by this issue for the past few days trying to sum up the rows based on this MAXX formula in PowerPivot.

highest Sched Prod Time (min):=MAXX(summarize(Stations,Stations[Station],"Duration1",IF(sum([prdDur])*2<[Station time In Cycle],[Station time In Cycle],sum([prdDur]))),[Duration1])/600

Basically I want it to sum up the productive duration by station over a period of time and if it is 2 times greater than the "station time in cycle" use the sum of that time and sum for the month the total time .

Issue I am having is if I break it down by every day in a month. I get the correct output for that day, but when I look at the summation of all of them, it is not correct. I believe what is happening is it takes the total productive duration of the entire month and compares it to the total "station time in cycle" and chooses the max between those instead of looking at each day.

Below are the values I have for each day and then the sum of the month. Month according to PowerPivot is 588.8, but if you were to sum the rows you would get 614.1.

How do I get the whole month the sum the individual dates by a station?

station 580

September	588.8
9/1/2015	20.4
9/2/2015	20.4
9/3/2015	20.4
9/4/2015	20.4
9/5/2015	20.4
9/6/2015	23.2
9/7/2015	22.5
9/8/2015	19.0
9/9/2015	19.0
9/10/2015	20.4
9/11/2015	20.4
9/12/2015	20.4
9/13/2015	20.4
9/14/2015	20.4
9/15/2015	20.4
9/16/2015	20.4
9/17/2015	20.4
9/18/2015	20.4
9/19/2015	20.4
9/20/2015	20.4
9/21/2015	20.4
9/22/2015	20.4
9/23/2015	20.4
9/24/2015	20.4
9/25/2015	20.4
9/26/2015	20.4
9/27/2015	20.4
9/28/2015	20.4
9/29/2015	20.4
9/30/2015	20.4

<tbody>
</tbody>

 

[scottsen]

I didn't totally understand the goal, so my answer is... a bit generic.

The general pattern for solving "hey, my grand total doesn't sum correctly" is
BetterResult := SUMX(VALUES(Calendar[Day]), [Result])

So, for any individual Day... the result is the same (SUMX on 1 item...). But for grand total, it will add the [Result] per day, to get a grand total.

 

[chryslerDAWGs]

I didn't totally understand the goal, so my answer is... a bit generic.

The general pattern for solving "hey, my grand total doesn't sum correctly" is
BetterResult := SUMX(VALUES(Calendar[Day]), [Result])

So, for any individual Day... the result is the same (SUMX on 1 item...). But for grand total, it will add the [Result] per day, to get a grand total.

Okay I will try to explain in a little more detail what I am looking at.

I have a table with events corresponding to dates.

For instance

Table name: Events

Date____Station_Dept_Event__PrdDur_PnpDur
1/1/2012 STA155 1210 IN CYCLE 655 655
1/1/2012 STA155 1210 Blocked 4234 4234
1/1/2012 STA284 2460 IN CYCLE 500 500
1/1/2012 STA155 1210 IN CYCLE 0 4584
1/1/2012 STA284 2460 Starved 6548 6548
1/2/2012 STA284 2460 IN CYCLE 3456 3456
1/2/2012 STA155 2460 IN CYCLE 320 320

Station time in Cycle:=calculate(sum([pnpDur]),Events[State]="IN CYCLE")

highest Sched Prod Time (min):=MAXX(summarize(Events,Events[Station],"Duration1",IF(sum([prdDur])*2<[Station time In Cycle],[Station time In Cycle],sum([prdDur]))),[Duration1])/600

etc and there are over 1 million of these events archived in the past few months.

What I need it to do is take the station with the highest PrdDur in each department for each day thus I used MAXX.
There can be 100+ stations but all I need is the one with the highest PrdDur for that day after summing all the PrdDur.
The IF statement is thrown in because for instance sometimes the "Station in Cycle" time has to replace the PrdDur if the PrdDur shows up as zero.
I want these to sum up to show my month total by department summing just the max duration of each day.

That's how I get the table in the original message where each day has a max for the line and then the sum at the top which is the issue since it does not sum up each day after evaluating the IF statement.

 

[scottsen]

I would give my "typical" answer above a try, and see how that works out for you. If it doesn't work out... we can figure it out

 

[chryslerDAWGs]

I would give my "typical" answer above a try, and see how that works out for you. If it doesn't work out... we can figure it out

I managed to get it to work using that concept. Thanks for the help!