Forum Discussion
Need Help: Fact and Dim Table Measure Visualization
I find a solution:
SUMX(DIM_PRODOTTO, RELATED(DIM_PRODOTTO_STOCK[Available_Qty])*RELATED(DIM_PRODOTTO_STOCK[Cost of Good]))
works for you?
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']))
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
I find a solution:
SUMX(DIM_PRODOTTO, RELATED(DIM_PRODOTTO_STOCK[Available_Qty])*RELATED(DIM_PRODOTTO_STOCK[Cost of Good]))
works for you?