cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Need Help: Fact and Dim Table Measure Visualization

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

1 ACCEPTED SOLUTION
Frequent Visitor

I find a solution:

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

works for you?

3 REPLIES 3
Super User

@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']))

Frequent Visitor

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

Frequent Visitor

I find a solution:

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

works for you?

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors