Forum Discussion
Standard deviation on average weighted price
- Anonymous7 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?
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).
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?