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

[hidalgo]

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 · Accepted solution]

@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. 

[kcantor · Accepted solution]

@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 · Accepted solution]

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]

@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.