Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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] )
)
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |