Forum Discussion

viferenc's avatar
viferenc
Icon for Helper II rankHelper II
4 years ago
Solved

Join two table with special criteria

Dear All,   I´m kindly asking for your support   I have two tables imported in Power BI Desktop.   in one of the table I have a list of transports done by the trucks. I have the transport ID, ...
Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, viferenc 

     

    You can try the following formula.

    KM diff between refueling = [Fueling KM high]-[Fueling KM low]
    Consumption (l/km) =
DIVIDE ( [Fueling (liter)], [KM diff between refueling] )

    Used KM =
IF (
    [Fueling KM high] >= [Ending KM],
    [Fueling KM high] - [Fueling KM low],
    [Fueling KM high] - [Starting KM]
)

    Used Liter = [Used KM]*[Consumption (l/km)]

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago

There are a lot of things wrong with this chart but it illustrates some of the issues with your request.

First of all the consumption is not linear between refuellings. But in the absence of further data that is what you would have to assume.  So you would have to take the first refueling at 119853  and its consumption (0.2379) but only for the interval between 119818 and 119853. Then all the consumptions up until the next refill after the trip is completed (which in your case coincides with the end of the trip but that is rather unlikely in real life), again prorated for only the fractional kilometers.

 

A lot of effort for an inherently imprecise result...

 

See attached for a failed attempt.