Need Help: Fact and Dim Table Measure Visualization

[inversia]

Hi Everyone,

I need your help to understand how build a mix visual between cols in Fact and Dim Table.

My model, if I build correctly, basically is:

• Fact Table: Orders Details (each row is a Product sold in a certain order_id and date)
• Dim Product: Product Dimension with Title, Brand and so on
• Dim Product Stock: each row contains the today Available Qty and Cost of good for each product 

Dim_Product_Stock is linked 1 to 1 with Dim_Product

Dim_Product is linked 1 to Many with Fact Table

I need to build the following viz:

• DIM_PRODUCT['Cluster']
• DIM_PRODUCT_STOCK['Available_Qty']
• DIM_PRODUCT_STOCK['Available_Qty']*DIM_PRODUCT_STOCK['Product_Cost']
• FACT_TABLE['Qta_Venduta']
• FACT_TABLE['Qta_Venduta'] * DIM_PRODUCT_STOCK['Product_Cost']

How can achieve that?

Thank you very much,

Alessandro

[amitchandak]

@inversia , You need to create measure for following

• Sum(DIM_PRODUCT_STOCK['Available_Qty'])
• Sumx(FACT_TABLE, related(DIM_PRODUCT_STOCK['Available_Qty']) *related( DIM_PRODUCT_STOCK['Product_Cost']) )
• Sum(FACT_TABLE['Qta_Venduta'])
• Sumx(FACT_TABLE, FACT_TABLE['Qta_Venduta'] * related( DIM_PRODUCT_STOCK['Product_Cost']))

[inversia]

Hi @amitchandak thanks you so much for your help.

I'm trying it, but it doesn't work, it seems that the SUMX multiplies each row of the FACT TABLE and not the summary. I try to explain better, simplifying dimensions and measures:

FACT TABLE (Date, SKU, Qty sold)

20240101, A, 2
20240304, A, 5
20240304, B, 10

DIM PRODUCT  (SKU, Title, Brand, Cluster)

A, Product A, Brand B, TOP_PRODUCT
B, Product B, Brand C, TOP_PRODUCT

DIM PRODUCT STOCK (SKU, Available_Qy, Cost of Good)

A, 10, €5
B, 5, €3

I think that the

Sumx(FACT_TABLE, related(DIM_PRODUCT_STOCK['Available_Qty']) *related( DIM_PRODUCT_STOCK['Product_Cost']) )

calculate as follows:

10*5

10*5

5*3

Total 115

So calculate the formula for each rows of the fact table, but in the visual, I need to show this:

Cluster, SUm of Available Qty, Sum of (Available Qty * Cost of Good), Sum of Qty Sold
TOP_PRODUCT, 15, 65€ (5*10+5*3), 17

How could achieve this?

Thank you so much again

[inversia · Accepted solution]

I find a solution:

SUMX(DIM_PRODOTTO, RELATED(DIM_PRODOTTO_STOCK[Available_Qty])*RELATED(DIM_PRODOTTO_STOCK[Cost of Good]))

works for you?