Standard deviation on average weighted price

[jopezzo]

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!

[Anonymous]

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]

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 · Accepted solution]

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?