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
Anonymous
Not applicable

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

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.

Community Support

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

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

Best Regards

Maggie

Frequent Visitor

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

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

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors