Forum Discussion

Jimcint's avatar
Jimcint
New Member
2 years ago
Solved

Calculating difference between rows based on multiple columns

I have a large table that contains mass measurements from multiple tires at multiple mileages, as shown below:   I would like to create two new columns (MassLossKG and MileDiff) that contain ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,

    PijushRoy , thanks for the concern about the question, and i want to offer some more infotmation for user to refer to.

    hello Jimcint , based on your description, you can refer to the following caucluated columns.

     

    MassLossKG =
VAR a =
    CALCULATE (
        SUM ( 'Table'[TyreWheelMassKG] ),
        ALLEXCEPT ( 'Table', 'Table'[TyreSpec], 'Table'[TyreNum] ),
        'Table'[StopId]
            = EARLIER ( 'Table'[StopId] ) - 1
    )
RETURN
    IF ( [Miles] <> 0, ABS ( [TyreWheelMassKG] - a ) )

    MileDiff =
VAR a =
    CALCULATE (
        SUM ( 'Table'[Miles] ),
        ALLEXCEPT ( 'Table', 'Table'[TyreSpec], 'Table'[TyreNum] ),
        'Table'[StopId]
            = EARLIER ( 'Table'[StopId] ) - 1
    )
RETURN
    IF ( [Miles] <> 0, ABS ( [Miles] - a ) )

     

    Output

    Best Regards!

    Yolo Zhu

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

     

Jimcint's avatar
Jimcint
New Member
2 years ago

For the fourth row of the table, tire C 4 is at 1600 miles. the mass is 22.34 kg. The previous measurement is 800 miles with a mass of 22.36 kg. so mass loss is 22.36 - 22.34 = 0.02 measurement distance is 1600 - 800 = 800. 

for the first (0 mile) measurement, there is nothing to subtract from, so the results would be null, 0, or blank. 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi,

PijushRoy , thanks for the concern about the question, and i want to offer some more infotmation for user to refer to.

hello Jimcint , based on your description, you can refer to the following caucluated columns.

 

MassLossKG =
VAR a =
    CALCULATE (
        SUM ( 'Table'[TyreWheelMassKG] ),
        ALLEXCEPT ( 'Table', 'Table'[TyreSpec], 'Table'[TyreNum] ),
        'Table'[StopId]
            = EARLIER ( 'Table'[StopId] ) - 1
    )
RETURN
    IF ( [Miles] <> 0, ABS ( [TyreWheelMassKG] - a ) )

MileDiff =
VAR a =
    CALCULATE (
        SUM ( 'Table'[Miles] ),
        ALLEXCEPT ( 'Table', 'Table'[TyreSpec], 'Table'[TyreNum] ),
        'Table'[StopId]
            = EARLIER ( 'Table'[StopId] ) - 1
    )
RETURN
    IF ( [Miles] <> 0, ABS ( [Miles] - a ) )

 

Output

Best Regards!

Yolo Zhu

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

 

  • Jimcint's avatar
    Jimcint
    New Member
    2 years ago

    Thank you. This is very helpful.