cancel
Showing results for
Did you mean:
Member

## Calculating fuel efficiency per row with multiple cars

Hi! I'm stuck. I have my car details since several years back where I want to calculate fuel efficiency in liters (Volume) per km (Odometer reading) for each row. I have created a new filtered table where I only have fill ups ("Tankning" in Swedish):

If it was only was ONE car I could solve it by creating an index column (1, 2, 3 etc) and then use:

Fuel efficiency =
Var CurrentOdometer = 'Table tankning only'[Odometer Reading]
Var LastOdometer = LOOKUPVALUE('Table tankning only'[Odometer Reading];'Table tankning only'[Index];'Table tankning only'[Index]-1)
RETURN
'Table tankning only'[Volume]/(CurrentOdometer-LastOdometer)

However this only works until I reach a new car, then my index reference is wrong. One solution for this is to create one new table per car, but that's not a good looking nor dynamic solution.

Do you have any hints for me to calculate fuel efficiency per row in the table above?

Johan

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Calculating fuel efficiency per row with multiple cars

assuming the Odometer Reading for a car can only grow, and you don't fill a car more than once a day this would work:

Fuel efficiency =
VAR CurrentCar = 'Table tankning only'[Bil]
VAR CurrentDate = 'Table tankning only'[Date]
VAR CurrentOdometer = 'Table tankning only'[Odometer Reading]
VAR LastOdometer =
CALCULATE (
MAX ( 'Table tankning only'[Odometer Reading] ),
FILTER (
'Table tankning only',
'Table tankning only'[Bil] = CurrentCar
&& 'Table tankning only'[Date] < CurrentDate
)
)
RETURN
DIVIDE ( 'Table tankning only'[Volume], CurrentOdometer - LastOdometer )

if you do fill in single car more than once a day then you can replace the Date filter with Index to get last entry for a given car that's lower than current Index

hope that helps

2 REPLIES 2
Super User

## Re: Calculating fuel efficiency per row with multiple cars

assuming the Odometer Reading for a car can only grow, and you don't fill a car more than once a day this would work:

Fuel efficiency =
VAR CurrentCar = 'Table tankning only'[Bil]
VAR CurrentDate = 'Table tankning only'[Date]
VAR CurrentOdometer = 'Table tankning only'[Odometer Reading]
VAR LastOdometer =
CALCULATE (
MAX ( 'Table tankning only'[Odometer Reading] ),
FILTER (
'Table tankning only',
'Table tankning only'[Bil] = CurrentCar
&& 'Table tankning only'[Date] < CurrentDate
)
)
RETURN
DIVIDE ( 'Table tankning only'[Volume], CurrentOdometer - LastOdometer )

if you do fill in single car more than once a day then you can replace the Date filter with Index to get last entry for a given car that's lower than current Index

hope that helps

Highlighted
Member

## Re: Calculating fuel efficiency per row with multiple cars

Excellent! I'm impressed. Just the kind of solution I wanted, now I have complete control of my car statistics.  I have my data in a Google spreadsheet which I feed using Google Form while filling fuel.

Just need to add a way to put BLANK() the first time a new car appears.

Thank you very much for your help!

/Johan