Standard Deviation of Calculated Measure Not Working

[JMac_WandB]

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

***although oddly enough the first two calculated measures in this method do work (but not the NTA_Ch)?

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

[v-shex-msft]

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

[Anonymous · Accepted solution]

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.