Forum Discussion

jopezzo's avatar
jopezzo
Helper I
7 years ago
Solved

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 ...
  • Anonymous's avatar
    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?