Reply
White Belt
Posts: 8
Registered: ‎11-23-2016

Joining Data Tables

[ Edited ]

I am trying to find the Turn Rate of products by vendor.  The calculation is product sales divided by the product inventory value (simple calculation Inventory qty * product cost).  The inventory qty and the product cost are on a "product table"  and the Sales Amount is on the "Sales by Item table".  I inner joined the two tables by product sku.  The issue is that there may be more than one sale by sku number and each time the sku number comes up in a sale the inventory for that item is counted twice, when I sum the inventory the amount of the inventory is wrong.  

 

I am not sure how to get the answer I am looking for.

 

Thanks for your help.

Green Belt
Posts: 55
Registered: ‎11-18-2015

Re: Joining Data Tables

You should be able to average or take the min or max of the inventory. Since the inventory number for each SKU should be the same each time it is duplicated, the avg, min, and max should all be equal.
White Belt
Posts: 8
Registered: ‎11-23-2016

Re: Joining Data Tables

This only works at the sku level, when I go up to the vendor level it give me the Average, or the Min, or the Max,  not the sum.  

Highlighted
Green Belt
Posts: 55
Registered: ‎11-18-2015

Re: Joining Data Tables

I don't think there's a way to get what you want without limiting the data in some way. But you can do a 'Group By' in your dataflow after the join and group it by SKU to get its inventory, sales, quantity sold, etc. in a certain time period. You could then have a drill down into your original dataset to get order and SKU details.

Announcements
Important! If you would like to update or change your Dojo user name simply click on My Settings here! Thank you!