cancel
Showing results 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

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):

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
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

Thank you for the kudos 🙂

2 REPLIES 2
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

Thank you for the kudos 🙂

Continued Contributor

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

Announcements

#### 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 Monthly Update - June 2024

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

#### 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.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors