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



Highlighted
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

Highlighted
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
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors