Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DimaMD
Solution Sage
Solution Sage

Amount of refuel in trip Correct calculation

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


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
2 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

@DimaMD 
Hello friend!

Hope this is what you're looking for

1.png

View solution in original post

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


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

@DimaMD 
Hello friend!

Hope this is what you're looking for

1.png

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


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

@DimaMD 
Which decision?

DimaMD
Solution Sage
Solution Sage

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


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Mahesh0016
Super User
Super User

Hello @DimaMD,

Can you share sample output in table format?

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


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors