Need some help in managing the relationships and calculating Grand Total%

[trulynaveen]

Hi Everyone,

Need some help in designing the relationship between by dataframe (df)- table and Inventory table. Please see below the following table and desired outputs.

df-table     

Month	Customer	StockCode	Forecast	Units Sold
Jan	ABC	GS-123	100	50
Jan	ABC	GS-456	20	30
Jan	ABC	GS-789	0	60
Jan	BXY	GS-123	50	50
Jan	BXY	GS-789	80	100
Feb	ABC	GS-123	100	60
Feb	ABC	GS-456	30	0
Feb	XYZ	GS-789	45	30
Feb	BXY	GS-456	60	72
Mar	ABC	GS-123	60	0
Mar	ABC	GS-789	75	60
Mar	XYZ	GS-123	40	40
Mar	XYZ	GS-456	60	70

Inventory-Table

StockCode	QOH	Unit Cost	QOH$
GS-123	500	2	$        1,000
GS-456	200	3	$           600
GS-789	400	4	$        1,600

Output-1:

Customer	SKU	Units Sold	Grand Total %	QOH$	Inv by Customer (QOH$*GrandTotal%)
ABC	GS-123	110	55%	$         1,000	$                       550
ABC	GS-456	30	17%	$            600	$                       105
ABC	GS-789	120	48%	$         1,600	$                       768
BXY	GS-123	50	25%	$         1,000	$                       250
BXY	GS-789	100	40%	$         1,600	$                       640
XYZ	GS-789	30	12%	$         1,600	$                       192
BXY	GS-456	72	42%	$            600	$                       251
XYZ	GS-123	40	20%	$         1,000	$                       200
XYZ	GS-456	70	41%	$            600	$                       244

Output-2

Customer	Inv by Customer
ABC	$                   1,423
BXY	$                   1,141
XYZ	$                       636

I would really appreaicte if anyone could walk me through the steps in establishing the relationship and calculating GrandTotal and Inventory by customer measures.

Thank you!

[v-juanli-msft]

Hi @trulynaveen 

Look at my pbix, there is something needed to be fixed,i will give the final outputs later.

Best Regards

Maggie

[trulynaveen]

Thank you. if I overlooked something here, let me know. Look forwar to see your final out put.

[Anonymous]

My initial thought is to link them by through the StockCode and initially set the directionality to both. You will be able to filter both ways and can test to see if that will yield you the results you are looking for. 

---

---