Calculating standard deviation of the calculated measure

[res.marcus]

Hi,

I´m trying to calculate the standard deviation of a calculated measure per variable.

I have a table like this:

Date	Sum of percentage in variable 1	Sum of percentage in variable 2	Sum of percentage in variable 3
201230	83,03094145	55,97272878	92,24959921
201231	73,84489647	51,94341392	89,37820926
201232	82,12073478	53,02193946	85,39993252
201233	84,68759472	48,88206236	90,67685169
201234	82,72898164	53,49932194	90,95449111
201235	83,16780315	61,96303925	90,53065014
201236	83,0222436	50,07188196	87,64743282
201237	87,80138219	58,30828708	87,8565807
201238	69,67478891	48,17744327	89,66835717
201239	77,23106208	48,57926405	94,64372303
201240	75,83743425	52,9489093	92,00524078
201241	98,60519992	61,07069471	88,33355519

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

And I would like to calculate the standard deviation of these values per vaiable. The requested result for variable 1 in this example is 7,067208565.

When I try to do standard deviation calculations I get one unique value for each week - see below.

Date	Standard deviation in variable 1	Standard deviation in variable 2	Standard deviation in variable 3
201230	9,316525234	7,352119492	8,057547247
201231	9,207568311	8,429782405	10,98418204
201232	8,750427961	7,513874159	8,509357209
201233	7,454698147	6,340021167	9,557455606
201234	9,183196767	7,64569251	8,434229083
201235	10,5959783	10,6020819	9,622005673
201236	8,357464059	5,613602674	8,517430214
201237	8,233784973	6,476803186	8,00956014
201238	7,056305771	6,842067964	10,06065965
201239	7,38807439	6,308675159	8,938462481
201240	7,940419973	7,766297405	11,25859248
201241	10,60519428	8,016290404	7,702836064

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

But what I really want is one value that I can use to identify large deviations in the calculated measure.


Does anyone know how to get it right?

Best,
Marcus

 

[NickyvV]

Hi Marcus,

how are you calculating those SD's? Is it a formula / measure over the the other measures you have in place?
Could you maybe get rid of the Date column? Is it in the row labels? The by deleting it there you would come up with the total SD per variable I believe?

HTH

 

[res.marcus]

Hi,

Thank you for your quick reply.

I´m currently calculating the values with the DAX standard deviation formula for a population. I cannot really get rid of the date column since i need it to display my other measure - also, even if get rid of it I don't get the results I´d expect - it seems to me that the standard deviation calculations are made on the underlying data (?). What I´m really after is a way to do calculations on the values visible in the table.

Best,
Marcus

 

[res.marcus]

Hi,

Could the SUMMARIZE function be used in combination with STDEV.P to generate the result I want?

Seems it should work, but I recieve the error message "Calculation error in measure 'Query'[004af71a-5719-4971-b309-0df239be4dcc]: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." when I try creating the measure: =SUMMARIZE(Query; Query[Account]; Query[Country]; Query[Industry]; Query[QuestionID]; Query[Question]; "grouped dev"; STDEV.P([Percentage]))

What am I doing wrong?

Best,
Marcus

 

[res.marcus]

Hi,

I just wanted to tell you that I solved my problem using =STDEVX.P(summarize(query; query[date]; query[segment]; query[account]; query[country]; query [industry]; query[questionid]; query[question]; "sum percent"; Sum(query[percentage])); [sum percent])

That is creating a table of the summarized values that I calculate the standard deviations for.

Many thanks to the DAX studio developers - the add-in really helped me understand what was going on!

Best,
Marcus

 

[MSchley]

I have a similar problem to the question above. i want to calculate the standard deviation of the most recent 12 previous QUARTERLY amounts given monthly data. My approach was to use an inner calculate along with the datesqtd function to first calculate the quarterly subtotals. Then I think I would use an outer calculate to filter to the last 12 values and calculate the standard deviation. My formula doesn't work. I wonder if someone can look it over and tell me what it is doing wrong. It looks like this:

CALCULATE (
STDEVX.P (
values(lnl[yyyymm]),
CALCULATE (
SUM ( lnl[amount] ),
DATESQTD ( lnl[date] )
)
),
DATESINPERIOD (
lnl[date],
ENDOFQUARTER ( lnl[date] ),
-12,
QUARTER
),
LNL[QtrMnth] <= 3
)

If I use the summarize function, is there still a way to respect the filters that get applied externally from the pivot table?

 

[scottsen]

Looking just at this part, it reads kinda weird to me:

STDEVX.P (
values(lnl[yyyymm]),
CALCULATE (
SUM ( lnl[amount] ),
DATESQTD ( lnl[date] )
)

It seems unlikely you mean QTD here, as that will give you a standard deviation over a weird series of running totals. Jan, Jan+Feb, Jan+Feb+Mar, Apr, Apr+May, ...

That will be a lot of deviation

You need to somehow pass a table of QUARTERLY values, so... probably... ?

CALCULATE(STDDEVX.P(VALUES(Calendar[QuarterNum]), [Total Amount]))

 

[MSchley]

I don't really follow what the first parameter of the standard deviation calculation is supposed to do. I tried changing the values in "YYYYMM" to actually be YYYYQQ, where the last two digits are the number of the quarter (01-04). That doesn't seem to work either. I also tried a simple formula like this:

=calculate(stdevx.p(values(lnl[YYYYQQ]),sum(lnl[amount])),all(lnl[date]))

and it didn't work either. I'm not sure what happens if I have more than one record with the same YYYYQQ. Does the standard deviation formula aggregate based on the first parameter? My goal is to determine the total for each quarter, and then take the standard deviation of those amounts. So just (Jan + Feb + Mar), (Apr + May + Jun), (Jul + Aug + Sept), and (Oct + Nov + Dec) for each year. Do I really need to use the summarize function to limit myself to just these quarterly totals?

 

[MSchley]

I think I have something working. I found a good article on DAX grouping that explains the VALUES and SUMMARIZE functions.

calculate(
stdevx.p(
values(table[YYYYQQ]),
calculate(sum(table[amount]))
),
DATESINPERIOD (
table[date],
ENDOFQUARTER ( table[date] ),
-12,
QUARTER
)
)