Sum column of rows only for rows that meet multiple conditions

[Justair07]

Hi,

I'm going to start with my original issue and maybe there is a simplier solution.

I'm using this formula to return QtyGood from the previous step:

Previous Step Qty = 
IF (
    Query2[PrevStepSequenceNum] = 0,
    Query2[StepQtyDue],
    LOOKUPVALUE (
        Query2[QtyGood],
        Query2[OrderNum], Query2[OrderNum],
        Query2[OrderStepSequenceNum], Query2[PrevStepSequenceNum],0
    )
)

It works great unless there are two values returned as you can see in this picture:

How can I have the alternate value for the Lookup to return the sum of these rows(1710 + 6220 = 7930)?

My first thought was to make a custom column that would sum the QtyGood based on the OrderNum and OrderStepSeqNum columns matching. But I don't know how to do this?

Is there an easier way, if not how can I create this custom column?

Thank you,

-Justair07

[v-shex-msft · Accepted solution]

Hi @Justair07 ,

It seems like you have set a static value as the result when lookupvalue not work, I'd like to suggest you use sum(Query2[QtyGood]) to return summary result.

Previous Step Qty =
IF (
    Query2[PrevStepSequenceNum] = 0,
    Query2[StepQtyDue],
    LOOKUPVALUE (
        Query2[QtyGood],
        Query2[OrderNum], Query2[OrderNum],
        Query2[OrderStepSequenceNum], Query2[PrevStepSequenceNum],
        SUM ( Query2[QtyGood] )
    )
)

Regards,

Xiaoxin Sheng

[Justair07]

@v-shex-msft I can't belive it was that simple. I didn't think that alternate value still took into consideration the previous conditions. thankyou so much!