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, truck ID and starting and endig KM of the transport

 

Example:

 

Transport IDTruck IDStarting KMEnding KM
2021-1252ABC-277119818120248

 

Second table shows me the refuling information of the trucks.

Truck IDFueling (liter)KM, when fueling was done
ABC-27794118 102
ABC-27765118 293
ABC-277132118 663
ABC-27778118 919
ABC-27770119 428
ABC-27749119 657
ABC-27747119 853
ABC-277134120 248
ABC-277177120 813
ABC-277149121 190
ABC-27775121 977
ABC-277112122 304

 

The problem is that during one transport the truck can refuel (we use the logic that they refuel it always to 100%) the tank not only once, but several times. It can happen that the fueling was done before the transport was started or during the transport.

 

In this exampleduring this transport the truck did it two times.

 

I would like combine the two table in the following way, because I would like to calculate the consumption (used liter) for each transport.

 

The blue columns are the combined table, the red columns are just calculation.

 

IDTruck IDStarting KMEnding KMFueling KM lowFueling KM highFueling (liter)KM diff between refuelingConsumption (l/km)Used KMUsed Liter
2021-1252ABC-27711981812024811965711985346,651960,24358,33
2021-1252ABC-277119818120248119853120248133,623950,34395133,62

 

It would be really a big, if somebody could help me.

 

Thank you in advance

 

Feri

 

 

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.

     

2 Replies

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

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    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.