Using related in DAX calculated measures

Question

I have loaded 2 tables into Power Pivot which are related by a key. One table contains sales for a product and the other contains it's cost. What I am trying to do is multiply the total sales by the cost of that item. How do I write a calculated measure in a pivot table that would calculate this?

= SUM(ProductSales[Sales])*RELATED(MarketValue[Value])

The error I am getting is that This formula is invalid or incomplete: 'The column 'MarketValue[Value]' either doesn't exist or doesn't have a relationship to any table available in the current context.'

I understand that this is wrong but i'm not sure how to modify it to suit my needs. I tried using SUM in front of the related function but it wouldn't accept the related function.

Thanks in advance.

Did you create the relationship between both tables in your model? — alejandro zuleta
This is would help you The column either doesn’t exist or doesn’t have a relationship to any table available in the current context. — Mohamed

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-09-12T15:09:50

If there is no relationship between the tables, you should have a common field at least, i.e. ProductSales[ProductID] and MarketValue[ProductID]. Using the common field you can join both tables using FILTER function.

=SUMX (
    ProductSales,
    ProductSales[Sales]
        * CALCULATE (
            VALUES ( MarketValue[Value] ),
            FILTER ( MarketValue, MarketValue[ProductID] = ProductSales[ProductID] )
        )
)

UPDATE: If you managed to create the relationship in your model, you have to create three measures:

Cost measure in the MarketValue.

= SUM(MarketValue[Value])
Sales measure in the ProductSales

=SUM(ProductSales[Sales])
Then just use the two previous measures.

= [Sales]*[Cost]

Let me know if this helps.

Using related in DAX calculated measures

1 Answers