- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

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

Showing results for

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Standard Deviation of Calculated Measure Not Worki...

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

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

Standard Deviation of Calculated Measure Not Working

06-24-2021
05:25 AM

Hi everyone! I'm hoping that someone can provide a simple solution to this as I have trawled literally hundreds of forums and 'solutions' to try to get this to work.

Put simply, I need to get the Standard Deviation of a calculated measure but none of the solutions offered appear to work. The field I need to analyse is the result of a number of calculated measures combined. When I attempt to use either STDEVX.P/S or even logic stepping STDEV using VARiables, I either get Blank or NaN results.

I see two logical solutions: either there is a way to apply the STDEV method to a calculated measure that I have not located or there is a way to create a new table which captures the calculated measure in a column that it can be applied to?

The column in question is NTA_Ch (this is the % change month on month of the NTA_Total_Return).

I can assure you that any variation of the following does not work:

NTA_Std_test = STDEVX.S(Summary, [NTA_Ch])

My attempts to create a temporary table to capture the data using the following also does not work:

tbl_Std_Dev = SUMMARIZE(Summary, Summary[Code], Summary[Cal_Period], Summary[Index], "NTA_TR", Calculate([NTA_Total_Return]), "SP_TR", calculate(Summary[SP_Total_Return]),"NTA_MoM", CALCULATE(Summary[NTA_Ch]))

I would be extremely grateful if someone had a solution to this issue.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Anonymous

Not applicable

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

06-24-2021
06:16 AM

Hi there

Let's get the obvious things out of the way first.

1) You do not follow the conventions that all DAX devs do and hence your code is much less readable and harder to understand. Rules for DAX Code Formatting - SQLBI

2) You are using SUMMARIZE to do calculations in it. That's very, very bad and is not recommended (will be discouraged by MS themselves in the future). Here are the reasons: All the secrets of SUMMARIZE - SQLBI

Now, to the point. You want to caclulate std of some measure. However, to do that you have to be able to say on which entities/attributes/rows you want to calculate the measure first. Because you can calculate something for a set of colors, or a set of dates, or a set of (vector) values that come from any combination of attributes. You have to be clear about what is the set of points on which you want to first calculate your measure and then caculate the std out of these values you've obtained from these points.

So, what are the points in your case? Let's say, for the sake of argument, that you want to calculate the std of measure M as calculated for the visible (**in the current context**) Period_Index values. Note that in the first row of you table above the current context contains just 1 Period_Index value, namely 59. If you calculate the measure for this point only you'll get 1 value out of the measure, right? So, the std will be 0. The same goes for the rest of the rows since each only sees 1 value of Peried_Index. The thing is that you won't get 0 in such a case but NaN or Inf. That's because the variance (from which the std comes) can be caculated as biased or unbiased estimator. If it's calculated as the latter one, the denominator in its formula is N - 1, where N is the number of points/values. Since there's only 1 point... Well, you get the idea.

So, to have a meaningful std you have to either use a biased estimator (where you divide by N) or have to have at least 2 points in the current context over which you calculate M. For 1 point you can return 0 just by using an IF statement in a suitable formula for your std.

Now, if you want to calculate the std of M for Period_Index, then you write this:

```
// This will return std for sets
// of Period_Index's which have
// more than 1 point. If there's
// only 1 point visible, BLANK
// will be returned. If you prefer
// to return 0, just add 0 as the
// third parameter under IF.
[Std of M] =
IF( NOT HASONEVALUE( T[Period_Index] ),
STDEVX.S(
VALUES( T[Period_Index] ),
[M]
)
)
// Note that this will correctly calculate
// std in the total row in the matrix
// since on that row all the values of
// Period_Index (from each row above)
// are visible.
```

2 REPLIES 2

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

06-28-2021
06:47 PM

Hi @JMac_WandB,

Did daxer's suggestion help with your scenario? if that is the case, you can consider Kudo or accept his suggestion to help others who faced similar requirements to find it more quickly.

If that not help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin

If this post**helps**, please consider **accept as solution** to help other members find it more quickly.

If this post

Anonymous

Not applicable

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

06-24-2021
06:16 AM

Hi there

Let's get the obvious things out of the way first.

1) You do not follow the conventions that all DAX devs do and hence your code is much less readable and harder to understand. Rules for DAX Code Formatting - SQLBI

2) You are using SUMMARIZE to do calculations in it. That's very, very bad and is not recommended (will be discouraged by MS themselves in the future). Here are the reasons: All the secrets of SUMMARIZE - SQLBI

Now, to the point. You want to caclulate std of some measure. However, to do that you have to be able to say on which entities/attributes/rows you want to calculate the measure first. Because you can calculate something for a set of colors, or a set of dates, or a set of (vector) values that come from any combination of attributes. You have to be clear about what is the set of points on which you want to first calculate your measure and then caculate the std out of these values you've obtained from these points.

So, what are the points in your case? Let's say, for the sake of argument, that you want to calculate the std of measure M as calculated for the visible (**in the current context**) Period_Index values. Note that in the first row of you table above the current context contains just 1 Period_Index value, namely 59. If you calculate the measure for this point only you'll get 1 value out of the measure, right? So, the std will be 0. The same goes for the rest of the rows since each only sees 1 value of Peried_Index. The thing is that you won't get 0 in such a case but NaN or Inf. That's because the variance (from which the std comes) can be caculated as biased or unbiased estimator. If it's calculated as the latter one, the denominator in its formula is N - 1, where N is the number of points/values. Since there's only 1 point... Well, you get the idea.

So, to have a meaningful std you have to either use a biased estimator (where you divide by N) or have to have at least 2 points in the current context over which you calculate M. For 1 point you can return 0 just by using an IF statement in a suitable formula for your std.

Now, if you want to calculate the std of M for Period_Index, then you write this:

```
// This will return std for sets
// of Period_Index's which have
// more than 1 point. If there's
// only 1 point visible, BLANK
// will be returned. If you prefer
// to return 0, just add 0 as the
// third parameter under IF.
[Std of M] =
IF( NOT HASONEVALUE( T[Period_Index] ),
STDEVX.S(
VALUES( T[Period_Index] ),
[M]
)
)
// Note that this will correctly calculate
// std in the total row in the matrix
// since on that row all the values of
// Period_Index (from each row above)
// are visible.
```

Announcements

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

Learn from experts, get hands-on experience, and win awesome prizes.

Featured Topics

Top Solution Authors

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

110 | |

102 | |

99 | |

38 | |

37 |

Top Kudoed Authors

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

158 | |

125 | |

76 | |

74 | |

63 |