Standard deviation on average weighted price

Hi!

I am trying to calculate a standard deviation on weighted average price per market.

I have the table below [Date]:

Date	Market	Scenario	Price	Quantity
1/01/2018	A	03. Mix	95.0	647.0
1/01/2018	B	03. Mix	100.0	844.0
1/01/2018	C	03. Mix	51.0	829.0
1/02/2018	A	03. Mix	57.0	19.0
1/02/2018	B	03. Mix	90.0	603.0
1/02/2018	C	03. Mix	66.0	102.0
1/03/2018	A	03. Mix	58.0	17.0
1/03/2018	B	03. Mix	61.0	742.0
1/03/2018	C	03. Mix	91.0	900.0

In order to calculate the standard deviation, I have create a measure for the average weighted price:

Weighted Average Price:

Weighted Average Price = 
DIVIDE(
		SUMX(Data,Data[Price]*Data[Quantity]),
		SUM('Data'[Quantity]),
        0
)

Then, I have a standard deviation measure as follows:

Standard deviation:

Standard deviation = STDEVX.P('Data',[Weighted Average Price])

However, I am not sure that these measures take into consideration the "Market" dimension.

Do you have any idea how to solve this issue?

Thanks!

Need Help

Anonymous

7 years ago

I created a new table. You would probably not actually need to create this table in the model, but it would be used in the measures since you need to pass a column to the standard deviation function.

Test Table = 
    ADDCOLUMNS(
        ADDCOLUMNS(
            SUMMARIZE( Table5, Table5[Market], Table5[Date]),

            /* Returns the total quantity of the specific market */
            "Total Quanitity for Specific Market", CALCULATE(SUM( Table5[Quantity]), ALLEXCEPT(Table5, Table5[Market])),

            /* Returns the total quantity, regardless of the market */
            "Total Qty for All Markets", SUM( Table5[Quantity]),

            /* Price * Quanitity Measure */
            "Total Sold (Price * Quantity)", CALCULATE( SUMX( Table5, Table5[Price] *Table5[Quantity]))
        ),
        /* Using the Price * Quantity measure, the denonminator is either the total sold for the specific market, or the grand total of quantity*/
        "Weighted Price Based on Total of All Qty",DIVIDE([Total Sold (Price * Quantity)] , [Total Qty for All Markets]),
        "Weighted Price Based on Qty of Market",DIVIDE([Total Sold (Price * Quantity)] , [Total Quanitity for Specific Market])
        )

Here's an explanation on what is happening in that function. Maybe a little closer to what you had in mind?

3 Replies

Anonymous

Not applicable

7 years ago

What would you want/expect the outcome to be on your sample above. I'm getting the following, but not sure it's correct or what you had in mind:

jopezzo

Helper I

7 years ago

The weighted average price is correct. The standard deviation looks a bit high to me, doesn't it?

What I am not sure about is how to make sure that the selected "Market" is taken into consideration before the calculations (for the weighted average price and for the standard deviation).

Anonymous

Not applicable

7 years ago

I created a new table. You would probably not actually need to create this table in the model, but it would be used in the measures since you need to pass a column to the standard deviation function.

Test Table = 
    ADDCOLUMNS(
        ADDCOLUMNS(
            SUMMARIZE( Table5, Table5[Market], Table5[Date]),

            /* Returns the total quantity of the specific market */
            "Total Quanitity for Specific Market", CALCULATE(SUM( Table5[Quantity]), ALLEXCEPT(Table5, Table5[Market])),

            /* Returns the total quantity, regardless of the market */
            "Total Qty for All Markets", SUM( Table5[Quantity]),

            /* Price * Quanitity Measure */
            "Total Sold (Price * Quantity)", CALCULATE( SUMX( Table5, Table5[Price] *Table5[Quantity]))
        ),
        /* Using the Price * Quantity measure, the denonminator is either the total sold for the specific market, or the grand total of quantity*/
        "Weighted Price Based on Total of All Qty",DIVIDE([Total Sold (Price * Quantity)] , [Total Qty for All Markets]),
        "Weighted Price Based on Qty of Market",DIVIDE([Total Sold (Price * Quantity)] , [Total Quanitity for Specific Market])
        )

Here's an explanation on what is happening in that function. Maybe a little closer to what you had in mind?

Forum Discussion

Standard deviation on average weighted price