Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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?
Thank you in advance,
Johan
Solved! Go to Solution.
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
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
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
User | Count |
---|---|
120 | |
69 | |
68 | |
57 | |
50 |
User | Count |
---|---|
166 | |
82 | |
68 | |
65 | |
53 |