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,
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:
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.
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 @v-shex-msft 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.
Help when you know. Ask when you don't!
Hello, @kentyler.
Thanks again for your assistance. Certainly, please see the excel data here:
https://1drv.ms/x/s!AlGraWJniC_whoAAGXWdhciYdSTKYQ?e=GYXUfy
Best Regards.
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 ?
Help when you know. Ask when you don't!
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |