Find On hand qty by a Unit of measure from two different table.

[Atinder]

Hello, 

I am trying  o build a report or table  where i can find what is my on hand qty by Unit of measure from these 2 tables.
Table 1 pulls data from the BC item ledger.
Table 2 pulls data from the shopify. 

Table 1:  is my Remaning qty from BC by Unit of measure. 

Item No	Unit of measure	Qty Per UOM	ON HAND Qty
Blue-001	2PK	2	20
Blue-001	2PK	2	10
Blue-001	2PK	2	10
Blue-001	2PK	2	18
Blue-001	2PK	2	22
Blue-001	2PK	2	26
Blue-001	2PK	2	30
Blue-001	3PK	3	10
Blue-001	3PK	3	10
Blue-001	3PK	3	15
Blue-001	3PK	3	19
Blue-001	3PK	3	23
Blue-001	3PK	3	27
Blue-001	3PK	3	31
Blue-001	4PK	4	25
Blue-001	4PK	4	12
Blue-001	4PK	4	20
Blue-001	4PK	4	20
Blue-001	4PK	4	24
Blue-001	4PK	4	28
Blue-001	4PK	4	32
Blue-001	Display	8	10
Blue-001	Display	8	15
Blue-001	Display	8	15
Blue-001	Display	8	21
Blue-001	Display	8	25
Blue-001	Display	8	29

Table 2 - on hand qty by sales channel sku. 

Channel SKU	Sales Channel Code	Qty Per UOM	BC  item  number	on Hand Qty
BLUE-001 2PK	Shopify	2	Blue-001	100
BLUE-001 3PK	Shopify	3	Blue-001	120
BLUE-001 4PK	Shopify	4	Blue-001	200
BLUE-001 Display	Shopify	8	Blue-001	150

Here is what i am trying to get 

Channel Sku	Qty Per UOM	Shopify On hand	BC on hand	Varraince ( Shopify - bc)
BLUE-001 2PK	2	100	136	-36
BLUE-001 3PK	3	120	135	-15
BLUE-001 4PK	4	200	161	39
BLUE-001 Display	8	150	115	35

[v-kaiyue-msft · Accepted solution]

Hi @Atinder ,

1. Create a measure value and calculate the Item ledger total.

Measure =
CALCULATE(SUM('Item Ledger'[On Hand Qty]),FILTER('Item Ledger','Item Ledger'[Item No.] = MAX('CSM Listing'[No.]) && 'Item Ledger'[Unit of Measure Code] = MAX('CSM Listing'[Unit of Measure])))

2. Create a measurement value and calculate the difference between the two.

Measure 2 = MAX('CSM Listing'[Total]) - 'CSM Listing'[Measure]

3. The final result is shown in the figure below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Atinder]

Hello @v-kaiyue-msft ,

Works and thank you!

[v-kaiyue-msft]

Hi @Atinder ,

Method 1: Create visual graphics objects.
1. Create the measure value and write the dax expression.

Measure =
MAX('Table 2'[on Hand Qty]) - SUM('Table'[ON HAND Qty])

2. Create a table visual and place the following fields into columns.

Method 2: Create a calculation sheet.
1. Create a calculation table and write the dax expression.

Table 3 =
VAR _t1 = SUMMARIZE('Table 2','Table 2'[Channel SKU],'Table 2'[Qty Per UOM],'Table 2'[on Hand Qty])
RETURN
_t1

2. Create a calculated column.

BC on hand = CALCULATE(SUM('Table'[ON HAND Qty]),FILTER(ALL('Table'),'Table'[Qty Per UOM]=EARLIER('Table 3'[Qty Per UOM])))

3. Create a calculated column.

Varraince = 'Table 3'[on Hand Qty] - 'Table 3'[BC on hand]

4. The final result is shown in the figure below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Atinder]

Hi @v-kaiyue-msft,

This solution only works for 1 item But I have 100 Sku's with different Unit of measure. I attached the sample file.   I am attaching sample data. 

From this sample data I am trying to get this. 

https://drive.google.com/file/d/1n8UbQsn6DLXxoi7TNCb5aRRFTB2EhvRk/view?usp=sharing