cancel
Showing results for
Did you mean:
Highlighted 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]:

 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!

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 =
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 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: Highlighted 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 =
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? Announcements #### 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?

We're excited to announce our first cross-community 'Can You Solve These?' challenge! #### 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

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5. Top Solution Authors
Top Kudoed Authors
Users online (1,434)