Forum Discussion

DaGemsta's avatar
DaGemsta
Icon for Helper I rankHelper I
4 years ago
Solved

Total column that sum's values from different rows

Hi guys

 

I'm working with the following data

 

IndexFuel InFuel OutOnBoard
89200520056383
904900011283
910011283
92013009983
93015008483
94015006983
953320010303
96808010303
97040006303
9809355368
99102010205398
1008000013398
1010013398

 

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 😞

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

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