3 Replies Latest reply on Mar 16, 2018 8:52 AM by alex.apolloni.0

# Stdev of an aggregate in a LOD calculation for use in a KPI and Filter?

Hi,

I'd like to do a get the stdev in a calculation so that it can be used as a Filter and to highlight marks like in the below mock up.

I have an aggregate calculation (in the example Superstore workbook it's [order/custmer] :=  count([Order ID])/countd([Customer Name]).  It's displayed in a chart split by Segment.

I'd like to have a Filter that said, "only show the charts for the segments where, for the previous month, the [order/customer] value is higher than the 1 standard deviation for [order/customer] for the whole date range."

The same calculation would also allow you to display marks for all the months where it was true (Sort of like the below screen shot) and in a KPI.

The calculation should be something like:

if ( [orders / customer] > ( [order/cust median (all)] + [order/cust stdev (all)]) )

then "greater than one stdev" else "less than one stdev"

end

My difficulty is that I can't get the either of the median or stdev calculations to work.

(i thought it'd be something like: STDEV({FIXED :[orders / customer]}) but that comes back as blank)

Thanks very much for your time.

Kind Regards,

Alex

• ###### 1. Re: Stdev of an aggregate in a LOD calculation for use in a KPI and Filter?

Hi Alex

I won't pretend to  understand the statistical analysis  but the issue with the std dev calc is in the fixed LOD

STDEV({FIXED  :zn([orders / customer])})  - the fixed portion of the statement returns a single number

i think you meant this - where it was fixed at the customer level then the stdev is taken over all customer

STDEV({FIXED [Customer Name] :zn([orders / customer])})

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Stdev of an aggregate in a LOD calculation for use in a KPI and Filter?

Hi Jim,

That's not giving me the number I was expecting.

to clarify:

I want to get the aggregate (count[order id]/countd[customer name])

of each month

and then get the median / stdev of those month values.

Thanks!,

Alex

• ###### 3. Re: Stdev of an aggregate in a LOD calculation for use in a KPI and Filter?

The answer i was looking for was here:   EXCLUDE Level of Detail Expressions

essentially, avg of months is:

The following EXCLUDE level of detail expression computes the average sales total per month and then excludes the month component:

`{EXCLUDE [Order Date (Month / Year)] : AVG({FIXED [Order Date (Month / Year)] : SUM([Sales])})}`