cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
trulynaveen Frequent Visitor
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 Team
Community Support Team

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
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
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 288 members 2,347 guests
Please welcome our newest community members: