How to create a virtual relationship between two fact tables

[carlovsky]

I have two Fact tables:

Both of them have a relationship with Product and Vendor Dimension

FACT_PURCHASE_ORDER_PRODUCT: Aggregated at Purchase Order and Product Level

SK_PURCHASE_ORDER	SK_VENDOR	SK_PRODUCT	QUANTITY_ORDERED
1458	1931	123	1500
1458	1931	124	1000
1458	1931	125	500
1459	1933	1000	500
1459	1933	951	1000
1460	1931	2141	500

FACT_SALES_PRODUCT: Aggregated at Vendor and Product Level

SK_VENDOR	SK_PRODUCT	L30D_ITEMS_SOLD
1931	129	500
1931	184	1200
1931	841	1200
1933	951	900

General considerations:

- One PO can contain multiple products, but it's always from the same Vendor.

- The combination from SK_VENDOR and SK_PRODUCT shown in FACT_SALES_PRODUCT is unique accross all the table

My goal:

Create a matrix visualization table at SK_PURCHASE_ORDER Level(Only with SK_PURCHASE_ORDER on the matrix), where I can display the quantities ordered in each PO + the L30D Items Sold from the Supplier related to that PO(When I mean all, I mean Total Supplier Sales in the L30D for ALL the products that he has sold, regardless they have a PO or not )

Example:

SK_PURCHASE_ORDER	QUANTITY_ORDERED	L30D_ITEMS_SOLD
1458	3000	2900
1459	1500	900
1460	500	2900

Any idea on how I can achieve this?

Thanks

Diego

[carlovsky]

Thanks a lot

[tamerj1 · Accepted solution]

Hi @carlovsky 
Please refer to attached sample file

L30D Sold Quantity = 
CALCULATE ( 
    SUM ( FACT_SALES_PRODUCT[L30D_ITEMS_SOLD] ),
    FACT_SALES_PRODUCT[SK_VENDOR] IN VALUES ( FACT_PURCHASE_ORDER_PRODUCT[SK_VENDOR] )
)

[carlovsky]

Hi again,

Inside Vendor Dimension I have a attribute called Manager.

The Manager field can be linked to one or more SK_VENDOR

 Following the same logic Based on your type of calculation, do you know how I could call for each table a related column from its dimension?

Do you know how I could get for this instance the L30D Items Based on Manager ?

Thanks

Diego