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.
Hi all,
I'd like to calculate the Weighted Average Retail Price which was derived from the 'Sales' and 'Price' tables. My data model is like:
Product Table |
- ProductID |
Date Table |
- Date |
Sales Table |
- ProductID - Date - Unit Sold |
Price Table |
- ProductID - Price Scrapped Date - Retail Price |
Can anyone help with this? Thanks in advance.
Solved! Go to Solution.
@Anonymous
You may refer to the measure below.
Measure =
DIVIDE (
SUMX ( 'Table', 'Table'[Qty Sold] * 'Table'[Retail Price] ),
SUM ( 'Table'[Qty Sold] )
)
@Anonymous , try Like
sumx(summarize('Product Table','Product Table'[ProductID],"_sum",sum('Sales'[Total Unit Sold]), "_avg",average('Price'[Retail Price])),divide(([_sum]*[_avg]),[_sum]))
Thanks amitchandak, I tried the formula but it only returns an Average instead of Weighted Average :(...
Could you please help look into it?
Here's one example of Week 13.
WeekDate | Retail Price | Qty Sold |
3/24/2019 | $162.99 | 0 |
3/25/2019 | $162.99 | 1 |
3/26/2019 | $162.99 | 2 |
3/27/2019 | $162.99 | 2 |
3/28/2019 | $159.99 | 0 |
3/29/2019 | $194.99 | 1 |
3/30/2019 | $194.99 | 0 |
Thanks again for the help.
@Anonymous
You may refer to the measure below.
Measure =
DIVIDE (
SUMX ( 'Table', 'Table'[Qty Sold] * 'Table'[Retail Price] ),
SUM ( 'Table'[Qty Sold] )
)
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 |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |