cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
trulynaveen
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     

 

MonthCustomerStockCodeForecastUnits Sold
JanABCGS-12310050
JanABCGS-4562030
JanABCGS-789060
JanBXYGS-1235050
JanBXYGS-78980100
FebABCGS-12310060
FebABCGS-456300
FebXYZGS-7894530
FebBXYGS-4566072
MarABCGS-123600
MarABCGS-7897560
MarXYZGS-1234040
MarXYZGS-4566070

 

Inventory-Table

StockCodeQOHUnit CostQOH$
GS-1235002 $        1,000
GS-4562003 $           600
GS-7894004 $        1,600

 

Output-1:

CustomerSKUUnits SoldGrand Total %QOH$Inv by Customer (QOH$*GrandTotal%)
ABCGS-12311055% $         1,000 $                       550
ABCGS-4563017% $            600 $                       105
ABCGS-78912048% $         1,600 $                       768
BXYGS-1235025% $         1,000 $                       250
BXYGS-78910040% $         1,600 $                       640
XYZGS-7893012% $         1,600 $                       192
BXYGS-4567242% $            600 $                       251
XYZGS-1234020% $         1,000 $                       200
XYZGS-4567041% $            600 $                       244

 

 

Output-2

CustomerInv 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.

BI-4.PNG

 

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
Community Support

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

Hi @trulynaveen 

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

 

Best Regards

Maggie

trulynaveen
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.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

April 2020 Community Highlights

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

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors