Sumproduct price x volume incorrect with DAX on summation level, weighted average price needed

[MichelBrown]

Hello all,

I try to calculate revenue over two fact tables per day and per week. The day revenue is correct but in the week revenue all sales volumes are summed up and multieplied with the sum of all prices in the week. Instead, I need a weighted average outcome. In Excel, on aggregated level, it would be something like : sumproduct(price;salesvolume)/sum(salesvolume).

The (simplified) data model looks like the below screenshot. 

I would appreciate any help to use a proper DAX formula that calculates correct revenue both on detailed as well as on aggregated level.

Thanks in advance for any support.

[AlexisOlson · Accepted solution]

You can achieve a sumproduct with SUMX to get an analogous calculation to your Excel formula.

WeightedAvgPrice =
DIVIDE (
    SUMX ( VALUES ( dim_Dates[Date] ), [Price] * [Volume] ),
    SUMX ( VALUES ( dim_Dates[Date] ), [Volume] )
)

[MichelBrown]

Hello Alexis,

Thank you so much for your solution, it works. I also appreciate you using the exact table and field names from my example which leaves no room for confusement. 

I already thought I had to do something with SUMX but I got stuck on what table(s) to use since I have one dimension and two fact tables. And didn't know that I have to use VALUES.