Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Standard deviation on average weighted price

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

**Weighted Average Price:** **Standard deviation:**

jopezzo

Helper I

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-24-2019
08:04 AM

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 = DIVIDE( SUMX(Data,Data[Price]*Data[Quantity]), SUM('Data'[Quantity]), 0 )

Then, I have a standard deviation measure as follows:

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!

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-30-2019
10:33 AM

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?

3 REPLIES 3

Highlighted
##

Anonymous

Not applicable

Re: Standard deviation on average weighted price

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-25-2019
02:13 PM

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
##

jopezzo

Helper I

Re: Standard deviation on average weighted price

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-27-2019
02:17 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-30-2019
10:33 AM

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?

Announcements

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

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

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

Top Solution Authors

User | Count |
---|---|

377 | |

123 | |

97 | |

93 | |

85 |

Top Kudoed Authors

User | Count |
---|---|

530 | |

191 | |

187 | |

149 | |

122 |