CODEWITHSUNDEEP
powerbidaxpowerquery
onlywynter
onlywynter

Reputation: 61

How to show stock movement from one plant to another, then back?

I have a large database of stock movements. I want to be able to see if we are sending stock back to the originating plant after it has already shipped the item out.

A miniature version of my data base it below:

Date Part No. Origin Destination Cost Quantity
1/29/2023 100 MIA MCO $500.00 500
1/29/2023 100 MIA ATL $450.00 500
1/30/2023 100 JFK MIA $700.00 500
1/30/2023 100 MCO SFB $700.00 500

the expected result would be an output table that summarizes the following:

Column A Column B
# of plants with inbound and outbound 2
inbound and outbound quantity 1500
inbound outbound % of volume 75%

Upvotes: 1

Views: 47

Answers (1)

davidebacci
davidebacci

Reputation: 30304

Here you go.

enter image description here

Create 3 measures as follows:

# of plants with inbound and outbound = 
VAR o = VALUES('Table'[Origin])
VAR d = VALUES('Table'[Destination])
RETURN COUNTROWS(INTERSECT(o, d))


inbound and outbound quantity = 
VAR o = VALUES('Table'[Origin])
VAR d = VALUES('Table'[Destination])
VAR i =  INTERSECT(o, d)
RETURN CALCULATE(SUM( 'Table'[Quantity]), 'Table'[Origin] IN i)

inbound outbound % of volume = 
[inbound and outbound quantity]/SUM('Table'[Quantity])

Upvotes: 1

Related Questions