Calculation based on Dimension and Fact Table

[joshua1990]

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)

[vivran22]

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