cancel
Showing results for
Did you mean:
New Member

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

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.

1 ACCEPTED SOLUTION
Memorable Member

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] )
)``````

2 REPLIES 2
Memorable Member

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] )
)``````

New Member

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.

Announcements

#### Welcome to the User Group Public Preview

Check out how to claim yours today!

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors