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:

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

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.


[TABLE="width: 1312"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD="align: right"]Standard deviation in variable 1[/TD]
[TD="align: right"]Standard deviation in variable 2[/TD]
[TD="align: right"]Standard deviation in variable 3[/TD]
[/TR]
[TR]
[TD]201230[/TD]
[TD="align: right"]9,316525234[/TD]
[TD="align: right"]7,352119492[/TD]
[TD="align: right"]8,057547247[/TD]
[/TR]
[TR]
[TD]201231[/TD]
[TD="align: right"]9,207568311[/TD]
[TD="align: right"]8,429782405[/TD]
[TD="align: right"]10,98418204[/TD]
[/TR]
[TR]
[TD]201232[/TD]
[TD="align: right"]8,750427961[/TD]
[TD="align: right"]7,513874159[/TD]
[TD="align: right"]8,509357209[/TD]
[/TR]
[TR]
[TD]201233[/TD]
[TD="align: right"]7,454698147[/TD]
[TD="align: right"]6,340021167[/TD]
[TD="align: right"]9,557455606[/TD]
[/TR]
[TR]
[TD]201234[/TD]
[TD="align: right"]9,183196767[/TD]
[TD="align: right"]7,64569251[/TD]
[TD="align: right"]8,434229083[/TD]
[/TR]
[TR]
[TD]201235[/TD]
[TD="align: right"]10,5959783[/TD]
[TD="align: right"]10,6020819[/TD]
[TD="align: right"]9,622005673[/TD]
[/TR]
[TR]
[TD]201236[/TD]
[TD="align: right"]8,357464059[/TD]
[TD="align: right"]5,613602674[/TD]
[TD="align: right"]8,517430214[/TD]
[/TR]
[TR]
[TD]201237[/TD]
[TD="align: right"]8,233784973[/TD]
[TD="align: right"]6,476803186[/TD]
[TD="align: right"]8,00956014[/TD]
[/TR]
[TR]
[TD]201238[/TD]
[TD="align: right"]7,056305771[/TD]
[TD="align: right"]6,842067964[/TD]
[TD="align: right"]10,06065965[/TD]
[/TR]
[TR]
[TD]201239[/TD]
[TD="align: right"]7,38807439[/TD]
[TD="align: right"]6,308675159[/TD]
[TD="align: right"]8,938462481[/TD]
[/TR]
[TR]
[TD]201240[/TD]
[TD="align: right"]7,940419973[/TD]
[TD="align: right"]7,766297405[/TD]
[TD="align: right"]11,25859248[/TD]
[/TR]
[TR]
[TD]201241[/TD]
[TD="align: right"]10,60519428[/TD]
[TD="align: right"]8,016290404[/TD]
[TD="align: right"]7,702836064[/TD]
[/TR]
</tbody>[/TABLE]


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