Sum with specific criteria

[viferenc]

Dear Community,

I would need your support.

Here you can see a snapshot from the table.

The refuel of the cars is not always at the same time as the route starts or ends. It could happen the refuel is only after several route.

I would need a support how I can sum up the KM between two refuel the in the column "correct KM for liter/km", marked yellow and green, only if I have the summed up KM can I calculate the correct liter/km, marked blue and yellow and this avg. liter/km I would like to show in the related route.

This logic should work for all licence number.

I hope I was somehow understandable

If you have a different/better solution I´m open for it.

Thank you for your support.

Feri

[ryan_mayu · Accepted solution]

@viferenc

here is a workaround for you.

1. create an index column in PQ

2. use dax to create a column

Column = 
VAR lastindex=MAXX(FILTER('Table','Table'[licence number]=EARLIER('Table'[licence number])&&'Table'[Index]<EARLIER('Table'[Index])&&NOT(ISBLANK('Table'[Refuel]))),'Table'[Index])
return if(ISBLANK('Table'[Refuel]),blank(),if(ISBLANK(lastindex),sumx(FILTER('Table','Table'[licence number]=EARLIER('Table'[licence number])&&'Table'[Index]<=EARLIER('Table'[Index])),'Table'[Distance KM]),sumx(FILTER('Table','Table'[licence number]=EARLIER('Table'[licence number])&&'Table'[Index]<=EARLIER('Table'[Index])&&'Table'[Index]>lastindex),'Table'[Distance KM])))

However, I am still not clear about the logic of last column in ur screenshot.

please see the attachment below

[ryan_mayu · Accepted solution]

@viferenc

here is a workaround for you.

1. create an index column in PQ

2. use dax to create a column

Column = 
VAR lastindex=MAXX(FILTER('Table','Table'[licence number]=EARLIER('Table'[licence number])&&'Table'[Index]<EARLIER('Table'[Index])&&NOT(ISBLANK('Table'[Refuel]))),'Table'[Index])
return if(ISBLANK('Table'[Refuel]),blank(),if(ISBLANK(lastindex),sumx(FILTER('Table','Table'[licence number]=EARLIER('Table'[licence number])&&'Table'[Index]<=EARLIER('Table'[Index])),'Table'[Distance KM]),sumx(FILTER('Table','Table'[licence number]=EARLIER('Table'[licence number])&&'Table'[Index]<=EARLIER('Table'[Index])&&'Table'[Index]>lastindex),'Table'[Distance KM])))

However, I am still not clear about the logic of last column in ur screenshot.

please see the attachment below

[viferenc]

Thank you very much.

It works perfec.t

With my last column I calculate the avg consumption (avg L/100km).  And this avg consumption, which is calculated in Index 3 (32,03 l/100km) I would like to show for Index 2 and Index 1, because it is related to them also. (in the same or in an extra column)

The same should work also for Index 4, the avg consunsuption (33,53 l/100km) which is calculated for Index 5, should be shown in index 4

I´m really appriciate for your support, it is a huge support.

Thank you 

Feri

[ryan_mayu · Accepted solution]

@viferenc 

please see the attachment