Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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] )
)
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] )
)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |