Standard Deviation of Values from Several Tables

[dont_forget]

I'm something of a PowerBI Desktop and DAX newbie, so please excuse me if this question is really simple.

I have five guys working for me, and I would like to create a visual that shows the number of calls each indivdiual handle per month on average, the average calls handled per month per person across the entire group, and finally the average calls plus one standard deviation and minus one standard deviation. I am able to do this in Excel, but I can't make it happen in PowerBI. The way the math works, I can figure out the average pretty easy. I'll just create one average measure for each individual, and then find the average of the five individual average measures. However in order to determine the standard deviation I need to figure out a way to feed all the values within a specifc column across five tables into one of the STDEV variants. I would very much like to do this in a way allows me to filter, and the average of averages updates as well as the STDEV.

My data is seperated into a table for each guy, and looks something like this:

Year    Month   Number of Calls

2016    Jan        455

2016    Feb       500

Thanks!

[v-shex-msft · Accepted solution]

Hi @dont_forget,

I modify the sample, perhaps you can take a look at below sample if it suitable for your requirement.

Merged table:

Table = UNION( ADDCOLUMNS(Sheet1,"Staff","Staff 1"), ADDCOLUMNS(Sheet2,"Staff","Staff 2"), ADDCOLUMNS(Sheet3,"Staff","Staff 3"))

Add calculate column to stored the date(use to sort).

Date = 
var temp=SWITCH([Month],"January",1,"February",2,"March",3,"April",4,"May",5,"June",6,"July",7,"August",8,"September",9,"October",10,"November",11,"December",12)
return
CONCATENATE([Year],if(LEN(temp)>1,temp,"0"&temp))

Measures.

Calculate the average:

Average = 
var currMonth=LASTNONBLANK('Table'[Month],[Month])
var currYear=LASTNONBLANK('Table'[Year],[Year])
return
 AVERAGEX(FILTER(ALL('Table' ),[Month]=currMonth&&[Year]=currYear),[Handled Calls])

Calculate the STDEV:

STDEV = 
var currMonth=LASTNONBLANK('Table'[Month],[Month])
var currYear=LASTNONBLANK('Table'[Year],[Year])
return
STDEVX.P(FILTER(ALL('Table' ),[Month]=currMonth&&[Year]=currYear),[Handled Calls])

Create a visual to show the result:

Regards,

Xiaoxin sheng

[MattAllington]

Can you please show a sample of your data tables. The answer depends on the table and column structure

[dont_forget]

Thanks for the quick response Matt. Here is one of the tables (the other four are identical):

Year	Month	LoggedOnTime	AHT	Handled Calls	Internal Calls	Transfered Calls	OutExtnCalls	Total Calls	Staffed Time	CPH
2015	January	359:05:34	0:04:36	595	20	15	75	705	359.09	1.96
2015	February	342:35:06	0:04:36	582	37	42	66	727	342.59	2.12
2015	March	329:08:06	0:04:32	595	18	31	78	722	329.14	2.19
2015	April	327:49:02	0:04:32	617	22	32	94	765	327.82	2.33
2015	May	251:25:18	0:04:24	523	13	27	52	615	251.42	2.45
2015	June	271:25:28	0:04:53	498	15	12	68	593	271.42	2.18
2015	July	377:50:33	0:04:24	797	35	31	85	948	377.84	2.51
2015	August	299:51:06	0:04:25	488	23	14	51	576	299.85	1.92
2015	September	316:19:16	0:04:24	578	9	20	51	658	316.32	2.08
2015	October	359:49:26	0:04:11	690	17	34	51	792	359.82	2.20
2015	November	297:32:24	0:04:29	490	27	26	58	601	297.54	2.02
2015	December	385:21:32	0:04:19	505	34	34	59	632	385.36	1.64
2016	January	614:27:46	0:04:41	548	38	31	56	673	614.46	1.10
2016	February	325:25:16	0:04:45	556	43	30	58	687	325.42	2.11
2016	March	389:46:44	0:04:37	644	39	29	77	789	389.78	2.02
2016	April	316:25:14	0:04:44	492	35	37	48	612	316.42	1.93
2016	May	370:01:10	0:04:21	611	42	34	7	694	370.02	1.88
2016	June	357:19:00	0:04:10	611	42	22	43	718	357.32	2.01
2016	July	260:30:30	0:04:33	517	27	35	50	629	260.51	2.41
2016	August	222:21:00	0:04:14	421	27	34	40	522	222.35	2.35
2016	September	348:54:14	0:04:22	616	49	56	47	768	348.90	2.20
2016	October	339:24:08	0:04:55	558	34	62	28	682	339.40	2.01
2016	November	389:57:46	0:04:50	479	38	47	37	601	389.96	1.54
2016	December	313:18:00	0:04:26	457	42	51	16	566	313.30	1.81

[MattAllington · Accepted solution]

Personally I would unpivot your data using Power Query before loading.

Select columns Handled, Internal, Transfere, OutExtn and unpivot them.  This will create 2 new columns "Call type" and Value.  You can then delete Total Calls as you can always recalc this by summing the new column.  Once you have all the calls in a single column, everything else becomes easier.

[v-shex-msft]

Hi @dont_forget,

Baed on your description, you want to get the each staff's average value per month, right?

If this is a case , you can refer to below steps.

1. Add staff column and merget the tables.

Table = Union(ADDCOLUMNS(Sheet1,"Staff","Staff 1"),ADDCOLUMNS(Sheet2,"Staff","Staff 2"),
ADDCOLUMNS(Sheet3,"Staff","Staff 3"),ADDCOLUMNS(Sheet4,"Staff","Staff 4"),ADDCOLUMNS(Sheet5,"Staff","Staff 5"))

2. Add measure to calculate each staff's monthly average.

Average = 
var currStaff=LASTNONBLANK('Table'[Staff],[Staff])
var currMonth=LASTNONBLANK('Table'[Month],[Month])
return
 AVERAGEX(FILTER(ALL('Table' ),[Staff]=currStaff&&[Month]=currMonth),[Staffed Time])

Avg - = [Average] - 100 

Avg + = [Average] + 100 

3. Create the visual to show the result.

Regards,

Xiaoxin Sheng

[dont_forget]

Hi Xiaoxin,

I'm actually looking to get the standard deviation of all 5 individuals calls per month. The average isn't a problem, as I can take an average of the average, and the math will work out such that it is accurate. I can't take the average of the individual's standard deviation. That would not be representative of the overal standard deviation. The standard deviation allows me to create a stastically informed upper control limit and lower control limt, rather than using a number like 100.

In excel I was able to do it by =STDEV.P(Staff1[Handled Calls],Staff2[Handled Calls],Staff3[Handled Calls],Staff4[Handled Calls]Staff5[Handled Calls]). I can't figure out a similar way to do the same syntax in DAX.

Very intersting way to pull up the average though. I learned something.

Thanks!

Nate

[v-shex-msft · Accepted solution]

Hi @dont_forget,

I modify the sample, perhaps you can take a look at below sample if it suitable for your requirement.

Merged table:

Table = UNION( ADDCOLUMNS(Sheet1,"Staff","Staff 1"), ADDCOLUMNS(Sheet2,"Staff","Staff 2"), ADDCOLUMNS(Sheet3,"Staff","Staff 3"))

Add calculate column to stored the date(use to sort).

Date = 
var temp=SWITCH([Month],"January",1,"February",2,"March",3,"April",4,"May",5,"June",6,"July",7,"August",8,"September",9,"October",10,"November",11,"December",12)
return
CONCATENATE([Year],if(LEN(temp)>1,temp,"0"&temp))

Measures.

Calculate the average:

Average = 
var currMonth=LASTNONBLANK('Table'[Month],[Month])
var currYear=LASTNONBLANK('Table'[Year],[Year])
return
 AVERAGEX(FILTER(ALL('Table' ),[Month]=currMonth&&[Year]=currYear),[Handled Calls])

Calculate the STDEV:

STDEV = 
var currMonth=LASTNONBLANK('Table'[Month],[Month])
var currYear=LASTNONBLANK('Table'[Year],[Year])
return
STDEVX.P(FILTER(ALL('Table' ),[Month]=currMonth&&[Year]=currYear),[Handled Calls])

Create a visual to show the result:

Regards,

Xiaoxin sheng