Calculation between three Fact Tables

[Anonymous]

Hello,

I have four tables:

fStock (Aggregated at Vendor and Product Level)

- SK_VENDOR

- SK_PRODUCT

- Stock Available

SK_VENDOR	SK_PRODUCT	Stock Available
123	100	50000
123	101	25000
124	110	3000
124	111	3500

fSales (Aggregated at Vendor and Product Level)

- SK_VENDOR

- SK_PRODUCT

- L7D Items Sold

SK_VENDOR	SK_PRODUCT	L7D ITEMS SOLD
123	100	250
123	101	300
123	102	400
124	103	1000
124	105	1200

fPurchaseOrderItem ( Aggregated at Purchase Order, Vendor and Product Level)

- SK_PURCHASE_ORDER

- SK_VENDOR

- SK_PRODUCT

- Quantity Ordered

SK_PURCHASE_ORDER	SK_VENDOR	SK_PRODUCT	Quantity Ordered
1	123	100	500
1	123	101	200
1	123	102	300
1	123	129	100

fInventory(Aggregated at Vendor and Product Level)

- SK_VENDOR

- SK_PRODUCT

- Units in Inventory

- Cost per Unit

SK_VENDOR	SK_PRODUCT	Units in Inventory	Cost per Unit
123	100	400	10
123	101	300	20
123	102	100	30
124	1001	299	34

What I need?

- Calculate the Stock Coverage per Days : fStock(Stock Available)/fSales(L7D Items Sold)/7

- Calculate the Stock Value for Products below 30 days of Coverage: fInventory(Units in Inventory)/fInventory(Cost per Unit) for Products where Coverage > 30 Days

- Finally, I need to show the Stock Value in a table Grouped by Purchase Order

Example:

SK_PURCHASE_ORDER	SK_VENDOR	QUANTITY ORDERED	Stock Value from Supplier > 30 days Coverage
1	123	1000	10000
2	123	300	10000

Is it possible with a calculated measure?

Important Notes:

- Coverage should be calculaged upon the combination of Vendor and Product

- One PO can contain multiple Products, but it is always from the same Vendor.

Thanks

Carlos

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

Here are my test you can refer to :

(1)For your first question. We can create a calculated column in 'fStock' Table :

Stock Coverage per Days = var _current_vendor = 'fStock'[SK_VENDOR]
var _current_product = 'fStock'[SK_PRODUCT]
var _t2= FILTER('fSales','fSales'[SK_VENDOR]=_current_vendor && 'fSales'[SK_PRODUCT] = _current_product)
return 
DIVIDE( 'fStock'[Stock Available] , SUMX(_t2,[L7D ITEMS SOLD]) * 7 )

 The result is as follows:

(2)For your second question . We can create a measure :

Measure = var _current_vendor = SELECTEDVALUE('fInventory'[SK_VENDOR])
var _current_product = SELECTEDVALUE('fInventory'[SK_PRODUCT])
var _ts =MAXX(FILTER(ALLSELECTED('fStock') , 'fStock'[SK_VENDOR]+_current_vendor  && 'fStock'[SK_PRODUCT]=_current_product),[Stock Coverage per Days])
return 
IF( _ts > 30  , DIVIDE(SUM('fInventory'[Units in Inventory]) , SUM('fInventory'[Cost per Unit]) ,BLANK()))

The result is as follows :

(3)For you last question . We can create a table:

Table = var _greate30 =DISTINCT( SELECTCOLUMNS( FILTER('fStock', 'fStock'[Stock Coverage per Days] >30 ) , "VENDOR" , [SK_VENDOR]))
var  _po_t =FILTER('fPurchaseOrderItem' , 'fPurchaseOrderItem'[SK_VENDOR] in  _greate30)
return 
SUMMARIZE(_po_t , [SK_PURCHASE_ORDER] , [SK_VENDOR] , "QUANTITY ORDERED" ,SUM('fPurchaseOrderItem'[Quantity Ordered]))

And then we can create a calculated column :

Stock Value from Supplier > 30 days Coverage = var _current_vendor = 'Table'[SK_VENDOR] 
var _t =SUMX( FILTER( 'fStock' , 'fStock'[Stock Coverage per Days]> 30 &&  'fStock'[SK_VENDOR]=_current_vendor ) , [Stock Available])
return _t

The result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hello

How are you so good on DAX ?

Thanks a lot