Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to create a virtual relationship between two fact tables

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_ORDERSK_VENDORSK_PRODUCTQUANTITY_ORDERED
145819311231500
145819311241000
14581931125500
145919331000500
145919339511000
146019312141500

 

FACT_SALES_PRODUCT: Aggregated at Vendor and Product Level

SK_VENDORSK_PRODUCTL30D_ITEMS_SOLD
1931129500
19311841200
19318411200
1933951

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_ORDERQUANTITY_ORDEREDL30D_ITEMS_SOLD
145830002900
14591500900
14605002900

 

Any idea on how I can achieve this?

 

Thanks

Diego

 

 

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    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] )
)

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks a lot

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 
    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] )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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