Weighted Average Price

[Anonymous]

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.

[v-shex-msft]

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

[Anonymous]

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.

[kentyler]

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.

[Anonymous]

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.

[kentyler]

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 ?