Forum Discussion

mb0307's avatar
mb0307
Responsive Resident
5 years ago
Solved

Substract based on criteria

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.

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    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.

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

    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])

     

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    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.