cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MichelBrown
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.

2021-03-01 17_27_21-Question weighted average.xlsx - Excel.jpg

 

1 ACCEPTED SOLUTION
AlexisOlson
Memorable Member
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] )
)

 

 

View solution in original post

2 REPLIES 2
AlexisOlson
Memorable Member
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] )
)

 

 

View solution in original post

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors