topic Re: Amount of refuel in trip Correct calculation in DAX Commands and Tips

Amount of refuel in trip Correct calculation

DimaMD — Fri, 16 Dec 2022 12:38:43 GMT

Hello community!

I need your help in correct count of amount of fuel in my table.

We have following data:

1) Vehicle

2) Dates

3) Type of operation

4) Refueling point

5) Start/end

6) Amount of refuel

7) Formula

We are interested in counting the amount of fuel which was refueled in points, which are not blank and not equal to "Base"

For example we have a trip which starts on 23.10.2022 and end's on 27.10.2022, so we have 1 row which is not blank and not equal to "Base". The result should be 49,16

The result of this count must be saved in cell of collum "Amount refuel", which row is marked as "End" in "Start/end" collum (in current table you can see "0" in that cell). So in the row of 27.10.2022 where type of operation is "Entrance to the base" and the "start\end" collum is marked as "end" we shold have amount of 49,16 in "amount refuel" cell, instead of "0"

Example file
Pbix File

Re: Amount of refuel in trip Correct calculation

Mahesh0016 — Fri, 16 Dec 2022 13:26:05 GMT

Hello DimaMD,

Can you share sample output in table format?

Re: Amount of refuel in trip Correct calculation

DimaMD — Fri, 16 Dec 2022 13:53:36 GMT

Hello Mahesh0016 Yes, Will view the attached excel file.
I highlighted trips in which refueling took place between the start of the trip and the end of the trip.
As a final result, we should see the total amount of fuel filled at the end of the trip

Re: Amount of refuel in trip Correct calculation

DimaMD — Mon, 19 Dec 2022 14:11:15 GMT

HI tamerj1 , Jihwan_Kim We need your help in solving the problem.

Re: Amount of refuel in trip Correct calculation

tamerj1 — Sat, 24 Dec 2022 13:46:39 GMT

DimaMD
Hello friend!

Hope this is what you're looking for

Re: Amount of refuel in trip Correct calculation

DimaMD — Mon, 26 Dec 2022 09:50:37 GMT

Hello Dear friend tamerj1
I am very happy for you, I thank you for your decision and the time allocated for this.
But during that time we achieved the goal ourselves.
We got a unique tip number and calculated the amount at the end of the flight.

Refueled in flight = -- Calculated column VAR _1 = CALCULATE( SUM ('Export'[Amount of fuel]), 'Export'[Type_of_operation] in {"Gas station","Entrance to the base"}, NOT 'Export'[Refueling point] in {"Base"}) return _1 Amount trip = -- measure VAR _1 = CALCULATE ( SUM( 'Export'[Amount_trip]), ALLEXCEPT('Export','Export'[ID_Trip])) return IF( [Start/End] = "End", _1 )

I think your solution will be valuable for other Power bi analysts

Re: Amount of refuel in trip Correct calculation

tamerj1 — Mon, 26 Dec 2022 11:44:47 GMT

DimaMD
Which decision?