DAX help please

[sjpaq]

I have a table called ResponseCosts. There is a column called Hours and another column called Rank. A second table RankHourlyRates keyed on Rank provides the Hourly Rate.

Total cost for a given row = ResponseCosts[Hours] x RankHourlyRates[Hourly Rate]

The following picture shows the relationship between the 2 tables in the model.

I created a new measure in the ResponseCosts table called ExtendedCost to store the total cost (hours * hourlyrate). It is defined as

ExtendedCost =

    SUMX(ResponseCosts, ResponseCosts[Hours] * SUMX(RankHourlyRates, RankHourlyRates[Hourly Rate]))

It is not aggregating properly as in the following table 

Can anybody help diagnose this? Thanks in advance.

[sjpaq · Accepted solution]

No responses, I think I have figured this out.

I changed the DAX formula to the following:

ExtendedCost =

    SUMX(ResponseCosts, ResponseCosts[Hours] *

            LOOKUPVALUE ('RankHourlyRates'[Hourly Rate],

            'RankHourlyRates'[Rank], [Rank]

and it works.