When summing a list of values only Grab first instance

[Anonymous]

I have a list of values coming through in a table that is a list of times for a trip.   Like below where Duration is the entire length of the trip in hours

Trip  Stop     Start           End             Duration

A       1          8:30 am      9:00am           2.5

A        2          9:10 am      10:00 AM       2.5

A        3           10:10 am     11:00 AM      2.5

I need to use the Duration in other calculations.  Like if stop 1 is a certain percentage of the duration.

How can I only pull any of those duration instances in my calculation?  I don't want to sum them since that would be duplicating things.

Does this make sense?  Below is my attempt at this... this only sums by Trip Number so it duplicates.  

SUMX(Distinct(FactAnalytics[TripNumber]),CALCULATE(SUM(FactAnalytics[Duration]))
)

[MFelix]

Hi @Anonymous ,

Looking at you data and being you value always the same you can use one of the below values:

Duration = MAX(FactAnalytics[Duration])

Duration = MIN(FactAnalytics[Duration])

Duration = AVERAGE(FactAnalytics[Duration])

However if the different lines have different values you need to add an agregator or a filter to get the needed result.

Can you share some addtional information about what you want to achieve?

Regards,

MFelix

[Anonymous]

@MFelix ,

Thanks for the reply and I would use those but I will need to test a lot to verify it won't mess up.  

Basically i have Trip Durations by Utilization percents.  I want to use this as part of a calculation to to say things such as for Driver A his utilization was x %.  So I would say Driver A has a max duration (fixed amount) i would use this to calculate how well they are utilized.

[MFelix]

Hi @Anonymous ,

Based on context (and this can be a different number of things) using one of the options I have refered should work however the information you have is very reduced so difficult to give you a better answer.

Can you share some more information or insights? 

Please see this post regarding How to Get Your Question Answered Quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 (courtesy of @Greg_Deckler).

Regards,

MFelix

[Anonymous]

Thanks @MFelix ,

I was preparing some sample data.  Please let me know how this looks.  I want to be able to sum up the hours by trip so they can be viewed at a month/day level but also by Driver and such.

So the max/min/average doesn't work.  Since it will grab the average of all the Durations at the Month level.  I would want it to sum each individual trip duration for the time period selected.  

StopNumber	TripNumber	Driver	RouteStart	RouteEnd	StopTimeHours
1	a	a	1/1/18 1:00 PM	1/2/18 4:42 PM	27.7
2	a	a	1/1/18 1:00 PM	1/2/18 4:42 PM	27.7
3	a	a	1/1/18 1:00 PM	1/2/18 4:42 PM	27.7
4	a	a	1/1/18 1:00 PM	1/2/18 4:42 PM	27.7		Work Hours	40
5	a	a	1/1/18 1:00 PM	1/2/18 4:42 PM	27.7		Total Duration	37.53
6	a	a	1/1/18 1:00 PM	1/2/18 4:42 PM	27.7		Utilized %	93.83%
7	a	a	1/1/18 1:00 PM	1/2/18 4:42 PM	27.7
8	a	a	1/1/18 1:00 PM	1/2/18 4:42 PM	27.7
9	a	a	1/1/18 1:00 PM	1/2/18 4:42 PM	27.7
10	a	a	1/1/18 1:00 PM	1/2/18 4:42 PM	27.7
1	b	b	1/2/18 2:50 AM	1/2/18 12:40 PM	9.83
2	b	b	1/2/18 2:50 AM	1/2/18 12:40 PM	9.83
3	b	b	1/2/18 2:50 AM	1/2/18 12:40 PM	9.83
4	b	b	1/2/18 2:50 AM	1/2/18 12:40 PM	9.83
5	b	b	1/2/18 2:50 AM	1/2/18 12:40 PM	9.83

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

You want to calculate total duration which should be 37.53 in your sample. Right? If I understand correctly, you could try the following formula.

Total Duration = 
var a = CALCULATETABLE(VALUES('Table'[StopTimeHours]),ALLEXCEPT('Table','Table'[TripNumber],'Table'[Driver]))
return 
SUMX(a,'Table'[StopTimeHours])

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

So that was working in the table but if I have a dashboard where this is part of one of my calculations it is ignoring all the filters on said dashboard.  So I can't pull up trips by dates or locations.  I see that is how AllExcept works but is there a way around this?