Forum Discussion

hidalgo's avatar
hidalgo
Regular Visitor
8 years ago
Solved

A ? for a Report: How to Subtract Returned Items Quantities

 

I have the following Purchasing Query that shows all rows -> purchases Receipts and Returns by Items.

I need to group by Items and sumarize the ReceiptQty. (UnitCost, ExtCost and Volume won't change)  BUT first I need to substract the Returned Items ReceiptQty (see the yellow ones)  from the Receipts Item ReceiptQty.

How do I do that in Power BI?

Thanks

  • kcantor's avatar
    kcantor
    8 years ago

    hidalgo

    In Query Editor create a conditional column for the if statement of Type.

    If [Type}="Return" then [ReceiptQty]*-1 else [ReceiptQty]

    Then you can group and the returns will be subtracted. 

  • hidalgo's avatar
    hidalgo
    8 years ago

    kcantor

     

    I created a conditional field as you told me called "Quantity"and it worked thanks. The returns now shows negative quantity.

    let
    Source = OData.Feed("------"),
    #"Post Purchases_table" = Source{[Name="Post Purchases",Signature="table"]}[Data],
    #"Added Conditional Column" = Table.AddColumn(#"Post Purchases_table", "Quantity", each if [Type] = "Return" then [ReceiptQty]*-1 else [ReceiptQty])
    in
    #"Added Conditional Column"

     

    THEN. I tried to create an aditional column called "Qty" for the report  to sumarize the Quantities so it could automatically substract those Returns with negative numbers BUT The designer shows the error in the picture below.

    How do I create that new Qty column to make it SUM those positive and negative Quantities?

3 Replies

  • kcantor's avatar
    kcantor
    Icon for Community Champion rankCommunity Champion
    8 years ago

    hidalgo

    In Query Editor create a conditional column for the if statement of Type.

    If [Type}="Return" then [ReceiptQty]*-1 else [ReceiptQty]

    Then you can group and the returns will be subtracted. 

    • hidalgo's avatar
      hidalgo
      Regular Visitor
      8 years ago

      kcantor

       

      I created a conditional field as you told me called "Quantity"and it worked thanks. The returns now shows negative quantity.

      let
      Source = OData.Feed("------"),
      #"Post Purchases_table" = Source{[Name="Post Purchases",Signature="table"]}[Data],
      #"Added Conditional Column" = Table.AddColumn(#"Post Purchases_table", "Quantity", each if [Type] = "Return" then [ReceiptQty]*-1 else [ReceiptQty])
      in
      #"Added Conditional Column"

       

      THEN. I tried to create an aditional column called "Qty" for the report  to sumarize the Quantities so it could automatically substract those Returns with negative numbers BUT The designer shows the error in the picture below.

      How do I create that new Qty column to make it SUM those positive and negative Quantities?

      • kcantor's avatar
        kcantor
        Icon for Community Champion rankCommunity Champion
        8 years ago

        hidalgo

        In your M code it does not appear that the conditional column for Quantity had the data type changed. By default, powerquery uses data type any. You will need to go into Query Editor and change the data type of your new conditional column to a number and then your calculation will work properly.