cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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:

Helper I

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
Not applicable

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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors