Calculating sum of paired items On the same Column

[Anonymous]

Hello Team,

I'm really struggling on how to do this in Power BI. Although Excel sheet is workable but I was hoping that I can do this in Power BI to have this automated.

Background, I have an Item ledger Entries table in our Business Central that I can get the data from. The products under the Item No is paired with each other. So we have Filled Cannisters that are sold, in turn customers return these cannisters and tagged as Empty Cannisters - each have their own quantity. I have 17 types of cannisters so that means that I have 34 Item numbers to work on. 🙂 

So what I aim to do is create a report that looks like this:

Posting Month <-> Filled Cannister Item No <-> Empty Cannister Item No <-> Sum Quantity of Filled Cannister <-> Sum Quantity of Empty Cannister <-> Delta  

The Delta will give m an idea how many cannisters are being returned to us. 🙂

Hope you can help me on this. 

Thank you in advance,

BR,

Abe

[Anonymous]

Are these items at the date level in your source, or at the month level? It sounds like you could just add a Date.MonthName  column in your items table, using your date column as a parameter. Then group on month name and Item Number. Then, you can add your 

sum aggregations. Then you can do your row arithmetic. 

--Nate

[Greg_Deckler]

@Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

Hello @Greg_Deckler ,

Sorry for that. Here is an example:

Raw table from Business Central Dynamics 365 looks like this:

Posting Date Entry Type Document Type Item No. Location Code Quantity

2/17/2022	Sale	Sales Shipment	FILLCAN01	WEST	-10
2/17/2022	Sale	Return Receipt	EMPCAN01	WEST	10
2/17/2022	Sale	Sales Shipment	FILLCAN02	EAST	-10
2/17/2022	Sale	Sales Shipment	FILLCAN03	WEST	-20
2/16/2022	Sale	Return Receipt	EMPCAN02	EAST	30
2/16/2022	Sale	Sales Shipment	FILLCAN01	EAST	-20
2/15/2022	Sale	Return Receipt	EMPCAN03	WEST	5

So as we can see, pair here is FILLCAN01-EMPCAN01, FILLCAN02-EMPCAN02... FILLCANXX-EMPCANXX. 

Inventory going out is negative (Sales Shipment). Returned Inventory is positive (Return Receipt). 

My final report/table may look like this:

Location Code Item No. Product Sold Product Returned Sum

WEST	FILLCAN01	-10	10	0
WEST	FILLCAN02	0	0	0
WEST	FILLCAN03	-20	5	-15
EAST	FILLCAN01	-20	0	-20
EAST	FILLCAN02	-10	30	20
EAST	FILLCAN03	0	0	0

Per above, the only one in the column is the FILLCANXX. Only the value of the FILLCANXX and the EMPCANXX is noted and I need to sum both Product Sold and Product Received to get the Delta (i.e., negative indicating customer returned more cannisters and positive indicating customer still owe us to return the cannisters). 

Hope this helps in describing my dilemma. 🙂 

Thanks in advance.

BR,

Abe

[Greg_Deckler]

@Anonymous So maybe:

Product Sold = SUMX(FILTER('Table',[Entry Type] = "Sales"),[Quantity])

Product Returned = SUMX(FILTER('Table',[Entry Type] = "Return"),[Quantity])

Sum = SUM('Table'[Quantity])

[Anonymous]

Thanks @Greg_Deckler ,

When I tried this out on PowerBI, I got the following result:

Location Code Item No. Product Sold Product Returned Sum

EAST	FILLCAN01	-20	-	-20
EAST	FILLCAN02	-	-	-
EAST	FILLCAN03	-20	-	-20
EAST	EMPCAN01	-	-	-
EAST	EMPCAN02	-	30	30
EAST	EMPCAN03	-	-	-
WEST	FILLCAN01	-10	-	-10
WEST	FILLCAN02	-10	-	-10
WEST	FILLCAN03	-	-	-
WEST	EMPCAN01	-	10	10
WEST	EMPCAN02	-	-	-
WEST	EMPCAN03	-	-	-

Unfortunately this is not what I was expecting to achieve. The key requirement here is to pair up the data between two joining items, let's say, FILLCAN01 and EMPCAN01 and sum it up resutling to the Delta that I was looking for.

Then the table will only display the Filled Cannister item numbers, the ordered quantity (FILLCANXX quantity), the quantity returned (EMPCANXX quantity) and the resulting Delta all in one row. 🙂 

Quite a feat to wrap my head around it to be honest. 🙂 

Thanks for spending time on this. Really appreciated. 

BR,

Abe