Total column that sum's values from different rows

[DaGemsta]

Hi guys

I'm working with the following data

Index	Fuel In	Fuel Out	OnBoard
89	2005	2005	6383
90	4900	0	11283
91	0	0	11283
92	0	1300	9983
93	0	1500	8483
94	0	1500	6983
95	3320	0	10303
96	80	80	10303
97	0	4000	6303
98	0	935	5368
99	1020	1020	5398
100	8000	0	13398
101	0	0	13398

I need to create a "Check" column that will cross check everything is as it should be.

In Excel this is easy as I use the following formula

Then I can see if column H matches column I. You can see from the excel version row 12 is 30 out, so that will be flagged and investigated.

I'm really struggling to get this working on PowerBi. I've created an Index column to help but just can't get my head around the formular.

I tried this calculated column

Check =

var ind = 'OnDemand'[Index]

var preind = ind-1

var postind = ind+1

var onboardPreRow =

SUMX(

FILTER(

'OnDemand',

'OnDemand'[Index] = preind

),

'OnDemand'[OnBoard]

)

var fuelinPostrow =

SUMX(

FILTER(

'OnDemand',

'OnDemand'[Index] = postind

),

[Fuel In]

)

var fueloutPostrow =

SUMX(

FILTER(

'OnDemand',

'OnDemand'[Index] = postind

),

[Fuel Out]

)

var subtotal = onboardPreRow - 'OnDemand'[OnBoard]

RETURN

subtotal + fuelinPostrow - fueloutPostrow

But its giving me an error about "a circular dependancy was detected OnDemand[Check]" so not sure what I've done wrong.

Can anyone help please? This is keeping me up 😞

[Ashish_Mathur]

Hi,

I am sure you have a Date column as well.  Please share that column in a revised table.

[v-cgao-msft · Accepted solution]

Hi @DaGemsta ,

Please try this.

Check = 
VAR _ind = 'OnDemand'[Index]
VAR _preind = _ind - 1
VAR _onboardPreRow =
    CALCULATE (
        MAX ( 'OnDemand'[OnBoard] ),
        FILTER ( ALL ( 'OnDemand' ), 'OnDemand'[Index] = _preind )
    )
VAR _fuelinPostrow =
    CALCULATE (
        MAX ( 'OnDemand'[Fuel In] ),
        FILTER ( ALL ( 'OnDemand' ), 'OnDemand'[Index] = _ind )
    )
VAR _fueloutPostrow =
    CALCULATE (
        MAX ( 'OnDemand'[Fuel Out] ),
        FILTER ( ALL ( 'OnDemand' ), 'OnDemand'[Index] = _ind )
    )
VAR _subtotal = _onboardPreRow + _fuelinPostrow - _fueloutPostrow
RETURN
    _subtotal

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data