Forum Discussion
Calculation between three Fact Tables
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
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 _tThe 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
2 Replies
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 _tThe 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