Forum Discussion

joshua1990's avatar
joshua1990
Icon for Post Prodigy rankPost Prodigy
5 years ago

Calculation based on Dimension and Fact Table

Hello everyone!

I would like to calculate the ratio between the actual Sales Value (fact) and the avg. Sales Value (Dimension).

We have a Sales table, that shows the actuals values for each item and each order

  • Date - Item - Value 

And we have a Dimension Table (Attibut List) that shows every dimension per code, also the avg. Sales Value that is updated once a year.

  • Item - Area - Costs - Avg. Value

 

Now I would like to calculate the ratio between the actual value and avg. Value.

Both tables are linked through the Item column.

 

Something like ?:

Ratio =
VAR Avg. Value = XXX
VAR Act. Value = XXX
RETURN
DIVIDE(Avg. Value, Act. Value)

 

 

Need Help

1 Reply

  • vivran22's avatar
    vivran22
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hello joshua1990 ,

     

    I have created a sample scenario:

     

    Order table:

     

    Product Table:

     

    Both related on Product ID

     

     

    Following is the measure:

    Ratio = 
VAR _Actual = SUM(Orders[Order Quantity])
VAR _Avg = AVERAGE(Product2[Avg Orders])
VAR _Ratio = DIVIDE(_Actual,_Avg)
RETURN
_Ratio

     

    In this, I have used Sum of Order Qty for Actual. You can choose to use any other aggregation.

     

    Result:

     

     

    Hope this is what you are looking for.

     

    Cheers!
    Vivek

    Blog: vivran.in/my-blog
    Connect on LinkedIn
    Follow on Twitter