Forum Discussion

inversia's avatar
inversia
Frequent Visitor
2 years ago
Solved

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

Need Help
  • inversia's avatar
    inversia
    2 years ago

    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

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    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's avatar
      inversia
      Frequent Visitor
      2 years ago

      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's avatar
        inversia
        Frequent Visitor
        2 years ago

        I find a solution:

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

         

        works for you?