Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Johanno
Continued Contributor
Continued Contributor

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
Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

2 REPLIES 2
Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Johanno
Continued Contributor
Continued Contributor

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.