Substract based on criteria

[mb0307]

Hi,

I have a Purchase Order (PO) data below:

If Movement Type is 102 then I want to find 101 value with same Batch and Quantity (in Confirmed Qty and Posting Qty columns). Substract Qty in Confirmed and Posting Qty columns.

So the result and sum should look like this:

Thanks in advance.

[v-janeyg-msft · Accepted solution]

Hi, @mb0307 

It’s my pleasure to answer for you.

According to your description,you can create a measure,then use it in filter pane.

Like this:

Measure =
IF (
    SELECTEDVALUE ( PO[Movement Type] ) = 101,
    VAR a =
        COUNTROWS (
            FILTER (
                ALL ( PO ),
                [Movement Type] = 102
                    && [Posting Quantity] = SELECTEDVALUE ( PO[Posting Quantity] )
                    && [Confirmed Quantity] = SELECTEDVALUE ( PO[Confirmed Quantity] )
                    && [Batch] = SELECTEDVALUE ( PO[Batch] )
            )
        )
    RETURN
        IF ( a > 0, 0, 1 ),
    IF (
        SELECTEDVALUE ( PO[Movement Type] ) = 102,
        VAR b =
            COUNTROWS (
                FILTER (
                    ALL ( PO ),
                    [Movement Type] = 101
                        && [Posting Quantity] = SELECTEDVALUE ( PO[Posting Quantity] )
                        && [Confirmed Quantity] = SELECTEDVALUE ( PO[Confirmed Quantity] )
                        && [Batch] = SELECTEDVALUE ( PO[Batch] )
                )
            )
        RETURN
            IF ( b > 0, 0, 1 )
    )
)

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[ryan_mayu]

@mb0307 

Do the requested and confirmed quantity of 102 always equals to the value of 101? If not, we can't delete those rows you hightlighted.

I am not sure if this is a good solution. Here is a workaround for your reference.

You can create three columns.

New Movement = 
if('Table'[Movement Type]=102,if(ISBLANK(MAXX(FILTER('Table','Table'[Movement Type]=101 && 'Table'[Batch]=EARLIER('Table'[Batch])),'Table'[Posting Quantity])),'Table'[Movement Type],101),'Table'[Movement Type])

New Posting Quantity = 
if('Table'[Movement Type]=102,if(ISBLANK(MAXX(FILTER('Table','Table'[Movement Type]=101 && 'Table'[Batch]=EARLIER('Table'[Batch])),'Table'[Posting Quantity])),'Table'[Posting Quantity],'Table'[Posting Quantity]*-1),'Table'[Posting Quantity])

New Request Quantity = 
if('Table'[Movement Type]=102,if(ISBLANK(MAXX(FILTER('Table','Table'[Movement Type]=101 && 'Table'[Batch]=EARLIER('Table'[Batch])),'Table'[Posting Quantity])),'Table'[Request Quantity],'Table'[Request Quantity]*-1),'Table'[Request Quantity])