cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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]:

 

DateMarketScenarioPriceQuantity
1/01/2018A03. Mix         95.0        647.0
1/01/2018B03. Mix       100.0        844.0
1/01/2018C03. Mix         51.0        829.0
1/02/2018A03. Mix         57.0          19.0
1/02/2018B03. Mix         90.0        603.0
1/02/2018C03. Mix         66.0        102.0
1/03/2018A03. Mix         58.0          17.0
1/03/2018B03. Mix         61.0        742.0
1/03/2018C03. 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!
 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Standard deviation on average weighted price

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?

Table with Explanations.png

View solution in original post

3 REPLIES 3
Highlighted
Anonymous
Not applicable

Re: Standard deviation on average weighted price

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:

Matrix 1.png

Highlighted
Helper I
Helper I

Re: Standard deviation on average weighted price

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).

Highlighted
Anonymous
Not applicable

Re: Standard deviation on average weighted price

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?

Table with Explanations.png

View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors