Forum Discussion
Weighted Average Price
Hi,
I am working on some price benchmarking of competitor products.
I am looking for some assistance to create a measure that results in a weighted average price: this should take into account not only the prices of all products with the same age, but also the ‘brand ranking’.
The weighted average price should be bias to rank 1, with less emphasis on rank 2 and 3.
In the example dataset below, a weighted average price for products 12 years in age should be formed of:
- 70% bias towards rank 1 prices
- 15% rank 2 prices
- 15% rank 3 prices
As you can see, not all product ages have price examples from each of the brand ranks.
How can we create a measure that recognizes this?
Can anyone help please? Thanks in advance.
5 Replies
Seems like you create an average price for the products in each of the ranks.
Then you take .7 * rank 1 average + .15 * rank 2 + .15 * rank 3
you can use a VAR statement to compute each average and in that statement check and see if there are not products to average set the average to 0 ?
Hi Anonymous ,
You can try to use following measure formula to add new column to calculate weighted sales, then you can use iteration function averagex to calculate weighted average.
Average = AVERAGEX ( ADDCOLUMNS ( CALCULATETABLE ( ALLSELECTED ( Table ), VALUES ( Table[Product Age] ) ), "Weighted", [Price] * SWITCH ( [Rank], 1, 0.7, 2, 0.15, 3, 0.15, 0 ) ), [Weighted] )Regards,
Xiaoxin Sheng
Thanks Anonymous and kentyler for your support.
I am still not succeeding with a solution. Perhaps it would be helpful if you can see my draft report. How would you suggest I create the measure in this report?
https://1drv.ms/u/s!AlGraWJniC_whf9_OhNK-eMCC1VTHw?e=NPspKy
Once again, thank you for your support.
The draft report is very helpful... but i cannot work with it would the excel file data sources. Is there any chance you can include them in the available files.
