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
v-juanli-msft
Community Support
Community Support

Hi @trulynaveen 

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

 

Best Regards

Maggie

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. 



Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors