How to aggregate grouped data with DAX measure. (Equivalent to Over Clause?)

[ShrewdOperator]

Hey there,

I was hoping someone could help with a measure I've been struggling with for the last few hours.

On PowerBI I want to use a measure (or use a calculated column if more suitable) to compare the total revenue values of each employee, and calculate the standard deviation on just the grouped figures of total revenue for each employee, which excel calulates it as 800.53.

Does anyone know how I might be able to get a measure that disregards the individual sales revenue figures and calculates the standard deviation just based on the total sum revenue figures of each employee?

The table below was created with Excel formulas but this is essentially what I want the measure to produce on PowerBI. 

Employee ID	Sum of Total Sales	Average of Totals	Standard Deviation of Totals
1	3100.00	1787.92	800.53
2	3102.30	1787.92	800.53
3	2363.04	1787.92	800.53
4	2000.00	1787.92	800.53
5	1749.87	1787.92	800.53
6	1641.43	1787.92	800.53
7	1387.77	1787.92	800.53
8	1299.25	1787.92	800.53
9	1283.61	1787.92	800.53
10	1203.11	1787.92	800.53
11	536.75	1787.92	800.53

I had no trouble creating the third column above for the Average of Totals; below was the measure I used to create that.  But to work out Standard Deviation of the totals using STDEV functionality has been difficult. 

Average Of Total Measure = 

CALCULATE(SUMx(SalesTable,
CALCULATE(SUM(SalesTable[Total]),ALL(SalesTable[Employee_ID]))
/DISTINCTCOUNT(SalesTable[Employee_ID])),
ALL(SalesTable[Employee_ID]))

As you've probably already guessed, I'm fairly new to some aspects of PowerBI and maybe I'm missing something very obvious, though I appreciate any help anyone can provide.

Thank you,

Shrew

[Greg_Deckler]

You just need to use the STDEV.S function instead of the STDEV.P function:

Measure 3 = STDEV.S(Table7[Sum of Total Sales])

No need to calculate the average the function will do that for you.

Would help though to know your source data that you are working with. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[ShrewdOperator]

Hey Greg,

Thank you for responding to my post.

So, I've tried using the StDEV.S/P in my measures but by themselves they aren't producing the single standard deviation value I'm looking for. 

With the reccomended measure you've given, it produces a distinct standard deviation value for each employee, which is based on their own respective sales totals. Like so below: 

Employee ID	StdDev of Employee Total
1	340.18
2	304.93
3	297.93
4	622.91
5	185.72
6	266.95
7	217.93
8	672.20
9	661.40
10	336.25
11	760.67
Grand Total	441.31

The measure I'm looking to create, the stages would go something like this. 

First, here is a sample of the source table which list all sales and it would be this table that the measure is based on.

OrderDate	Region	Employee ID	Item	Units	Unit Cost	Total
06/01/2016	East	2	Pencil	95	1.99	189.05
23/01/2016	Central	3	Binder	50	19.99	999.5
09/02/2016	Central	4	Pencil	36	4.99	179.64
26/02/2016	Central	5	Pen	27	19.99	539.73
15/03/2016	West	6	Pencil	56	2.99	167.44
01/04/2016	East	2	Binder	60	4.99	299.4
18/04/2016	Central	7	Pencil	75	1.99	149.25
05/05/2016	Central	4	Pencil	90	4.99	449.1
22/05/2016	West	8	Pencil	32	1.99	63.68
08/06/2016	East	2	Binder	60	8.99	539.4
25/06/2016	Central	9	Pencil	90	4.99	449.1

From the source table I want to work out the total revenue for each employee, like below:

Employee ID	Sum of Total
2	2363.04
3	3109.44
4	4406.71
5	1749.87
6	1283.61
7	1299.25
8	1203.11
9	1387.77
10	536.75
11	3102.30
12	1641.43
Grand Total	22083.29

I then want to take those summed figures of each employee and calculate the standard deviation on just the the summed fugures. Which would look something like this below: 

Employee ID	Sum of Total	Standard Deviation
2	2363.04	1075.16
3	3109.44	1075.16
4	4406.71	1075.16
5	1749.87	1075.16
6	1283.61	1075.16
7	1299.25	1075.16
8	1203.11	1075.16
9	1387.77	1075.16
10	536.75	1075.16
11	3102.30	1075.16
12	1641.43	1075.16

The average that you saw in my first post is to be used for other purposes seperate from the standard deviation.

Please let me know if there's something I've not covered and thank you for your help. 

Shrew,

[ShrewdOperator · Accepted solution]

Hey Guys,

I just wanted to update on my post. After doing a bit more digging I eventually got my solution, though I'm unsure if it's the best approach. 

Nevertheless I ended up creating a seperate table with just the employee details and created a sum measure on the main table of sum total per employee:

I then created a new column in the new table and referenced that measure:

And I was then able to do a simple dax formula to find standard deviation of the summed values like below to get the figures I needed:

Again like I say, I'm not sure if this is the most elegant way of performing this but there wasn't that much literature out there that discusses what I was looking for; so if anyone has any advice about a simpler, more optimal way of perform this, please let me know. 

Thanks, 

Shrew