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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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