cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Johanno Member
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):

Capture.JPG

 

 

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?

 

Thank you in advance,

Johan

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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
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
Johanno Member
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. Smiley Happy 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