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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.