Averages Weirdness: How much fuel is left?

[zudar]

Hi all,

I could use some help with the following analysis:

In the top left corner 'Usage', you see how much fuel ('Liters') a vehicle used between two fuel tanking moments and for how long ('Time').

There is another table 'Locations', to the right, that gives the current location of every vehicle.

Now I have to determine the average usage rate (Liters/Time) per Vehicle Type and Location (with some extra exceptions) and multiply the corresponding result with the Time in the record that has no 'Next Date' and no 'Liters'.

What I did was create all calculated columns:

Last location = RELATED(Location[Location])

... to get the last location into my 'Usage' table. 

And:

Usage Rate = DIVIDE(Usage[Liters], Usage[Time])

... to determine usage rate per record in the 'Usage' table.

And:

Average Usage Rate = CALCULATE(AVERAGE(Usage[Usage Rate]),
FILTER(Usage, 
Usage[Type] = EARLIER(Usage[Type])
&& Usage[Location] = EARLIER(Usage[Last location])
&& Usage[Liters] <= 100
&& (Usage[Usage Rate] < 14 || Usage[Usage Rate] >= 8)
))

  
... to determine the average usage rate for a Vehicle Type, 'Last' location, where not more than 100 Liters was fueled and the Usage rate was between 8 and 14 to take out the outliers.

So:

Used Liters = Usage[Average Usage Rate] * Usage[Time]

... this became the formula to calculate the used liters for the record with no 'Next Date' and no 'Liters'.

And:

Tank % = DIVIDE(Usage[Tank]-Usage[Used Liters],Usage[Tank])

... this became the formula to calculate how much percent of the fuel tank is still left.

The table 'Average Usage Rates per Type & Location' is the intermediate result that shows the resulting average usage rates that are used to determine how much fuel was used in the last record (per vehicle) and how much of the fuel tank is still filled.

The table 'Results' is the final table that I'm looking to create. It is filtered using 'Next Date' is (Blank). To give an overview of every vehicle and how much fuel is still left in the tank.

All seemed well, until I looked deeper into vehicle 243. Its last location is X and it's a Type B vehicle. My calculation gives an average usage rate of 8,8 liters per hour.. but when I check the usage table manually (see below), it appears that the record with usage rate 5,6 was included, when it wasn't supposed to. The correct usage rate should be based on only one record, resulting in 12 liters/hour, giving 12*5 = 60 Used Liters and 40% left in the fuel tank.



Anyone sees what is happening here?

Your help is very much appreciated. The pbix-file can be downloaded here.

[HotChilli · Accepted solution]

||   or  && ?

[zudar]

I feel deeply ashamed about this and will try to forget about it ASAP. 😂

Thank you!