Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
inversia
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

I find a solution:

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

 

works for you?

View solution in original post

3 REPLIES 3
amitchandak
Super User
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']))

 

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?

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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