Forum Discussion

Daptoid's avatar
Daptoid
Frequent Visitor
5 years ago
Solved

Calculating difference between numbers in different rows, different columns with filters - HELP!

  Hi Power BI users. I am hoping for some assistance as I am stumped for a solution to my problem.   I have a table of fleet car use. The table contains various different cars. Entries are usuall...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Daptoid 

    Try to build calculated columns to achieve your goal.

    Firstly build a rank column.

    Rank = RANKX(FILTER('Table','Table'[Vehicle]=EARLIER('Table'[Vehicle])),'Table'[ KMS at start],,ASC)

    Then build Expection column as below.

    Expection = 
VAR _Vehicle = 'Table'[ KMS at start]
VAR _NewVehicle =
    CALCULATE (
        SUM ( 'Table'[KMS at end] ),
        FILTER (
            'Table',
            'Table'[Vehicle] = EARLIER ( 'Table'[Vehicle] )
                && 'Table'[Rank]
                    = EARLIER ( 'Table'[Rank] ) - 1
        )
    )
RETURN
    IF ( 'Table'[Rank] = 1, 0, _Vehicle - _NewVehicle )

    Result is as below.

    Best Regards,

    Rico Zhou

     

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Daptoid 

Try to build calculated columns to achieve your goal.

Firstly build a rank column.

Rank = RANKX(FILTER('Table','Table'[Vehicle]=EARLIER('Table'[Vehicle])),'Table'[ KMS at start],,ASC)

Then build Expection column as below.

Expection = 
VAR _Vehicle = 'Table'[ KMS at start]
VAR _NewVehicle =
    CALCULATE (
        SUM ( 'Table'[KMS at end] ),
        FILTER (
            'Table',
            'Table'[Vehicle] = EARLIER ( 'Table'[Vehicle] )
                && 'Table'[Rank]
                    = EARLIER ( 'Table'[Rank] ) - 1
        )
    )
RETURN
    IF ( 'Table'[Rank] = 1, 0, _Vehicle - _NewVehicle )

Result is as below.

Best Regards,

Rico Zhou

 

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

  • Daptoid's avatar
    Daptoid
    Frequent Visitor
    5 years ago

    Hi Anonymous This looks like quite a good option for my use case. I will be trying this as soon as I am next in the office. Thanks for your time and effort!