cancel
Showing results for
Did you mean:
Frequent Visitor

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

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!

3 REPLIES 3
Community Support

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

Best Regards

Maggie

Frequent Visitor

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

Anonymous
Not applicable

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.

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks