How to sum values according to other column's value

[Anonymous]

hi guys!

i have been stuck with a particular case with some data in a database i'm trying to graphicate, the thing is, at my job i need to know how much fuel is every car consuming monthly but based on how many kms/mi has traveled vs how many times did load fuel in the same month, so based on this info, it should look like this, kms of car at the beginning of the month, vs kms of car at the end of the month / fuel consumption during month , thing is a car in order to complete some trips it needs to reload fuel every few days so we'd have a duplicate register in fuel column, so what i need to do is to sum the times the car reloaded fuel to make it a total up to the most recent date, so i can filter by car name, i'll write some table so you can see what i want vs what i have

TABLE EXAMPLE OF CURRENT DATA

CAR N°	DATE	FUEL	KMS
1	02/11	40 LTS	200 KMS
2	05/11	20 LTS	80 KMS
3	07/11	35 LTS	50 KMS
1	09/11	40 LTS	150 KMS
2	08/11	45 LTS	250 KMS

And this is what i would like to have so i can handle the data more easily

CAR N°	DATE	FUEL	KMS
1	09/11	80 LTS	350 KMS
2	05/11	20 LTS	80 KMS
3	08/11	65 LTS	330 KMS

any advice on this info is truly helpful, thanks in advance.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try below steps:

1. below is my test table

Table:

2.create measure with below dax formula

Measure 2 = INT( LEFT(SELECTEDVALUE('Table'[FUEL]),2))

Measure =
VAR cur_car =
    SELECTEDVALUE ( 'Table'[CAR N°] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [CAR N°] = cur_car )
VAR tmp2 =
    ADDCOLUMNS ( tmp, "Car", [CAR N°], "_Fuel", [Measure 2] )
VAR _a =
    SUMX ( tmp2, [_Fuel] ) & " LTS"
RETURN
    _a

Measure 3 = INT( LEFT(SELECTEDVALUE('Table'[KMS]),3))

Measure4 =
VAR cur_car =
    SELECTEDVALUE ( 'Table'[CAR N°] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [CAR N°] = cur_car )
VAR tmp2 =
    ADDCOLUMNS ( tmp, "Car", [CAR N°], "_KMS", [Measure 3] )
VAR _a =
    SUMX ( tmp2, [_KMS] ) & " KMS"
RETURN
    _a

3. add a table visual with "Table[CAR N°]" and "Table[Date]" field and measure.

set "Table[Date]" is latest

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi! thanks for your reply, i'm trying out the formula and tell you the outcome

[Smalfly]

Hi @Anonymous ,

you can achieve that by using the DAX summarize function with :

• Car No as your groupby_columnName
• Max (date)
• Sum(Fuel)
• Sum(KMS)