Dynamic standard deviation - DAX

[Anonymous]

Hey Guys,

I have to represent the average and stadand deviation of the Lead Time deliveries in a graph for each supplier and product. And this average and standard deviation would change according to the slicer of the Purchase Order Date.

I have this spreadsheet.

Material Code	Supplier	Puchase Order	Purchase Order Date	LT
A	X	111111	24/09/2020	40
A	X	111112	24/09/2020	50
A	X	111113	4/11/2020	16
A	X	111114	1/12/2020	34
A	X	111115	24/02/2021	2
A	X	111116	12/07/2021	13
A	Y	111117	7/12/2020	163
A	Y	111118	7/12/2020	163
A	Y	111119	7/12/2020	158
A	Y	111120	15/12/2020	154
A	Y	111121	16/05/2021	116
B	XX	111122	21/09/2020	92
B	XX	111123	2/10/2020	41
B	XX	111124	2/11/2020	50
B	YY	111125	21/11/2020	38
B	YY	111126	22/01/2021	18
B	YY	111127	7/02/2021	26
B	YY	111128	16/03/2021	20
B	YY	111129	16/03/2021	41
B	YY	111130	4/04/2021	36
B	YY	111131	21/04/2021	26
B	YY	111132	27/04/2021	31
B	YY	111133	10/05/2021	8
B	YY	111134	8/07/2021	11
B	YY	111135	2/08/2021	28

Basically, I would need a graph like that. 

However, I cannot calculate the standard deviation of each supplier and material code, just the average is working. 

I calculated successfully the average by using two formulas:

Average_Aux = CALCULATE(AVERAGEX(Table,Table[LT]),ALLEXCEPT(Table,Table[Supplier],Table[Material Code],Table[Purchase Order Date]))

Average = AVERAGEX(SUMMARIZE(Table,Table[Supplier],"name",Table[Average_Aux]),[Average_Aux])

I tried to reply for the standard deviation P. The first formula has worked, and the I see the standard deviation of the product in the graph. But when I apply the second formula to filter per supplier, the result is zero.

[v-jingzhang · Accepted solution]

Hi @Anonymous

Create two measures rather than calculated columns with below DAX.

Average = CALCULATE(AVERAGE('Table'[LT]),ALLEXCEPT('Table','Table'[Supplier],'Table'[Material Code],'Table'[Purchase Order Date]))


Standard Deviation = CALCULATE(STDEV.P('Table'[LT]),ALLEXCEPT('Table','Table'[Supplier],'Table'[Material Code],'Table'[Purchase Order Date]))

Please refer to the attached PBIX file. When I captured the following screenshot, I put Material Code in the X axis. But later I found it should be Supplier instead, so I replaced the column in the PBIX directly without capturing a new screenshot as the settings are totally the same. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@Anonymous , Try like

Average = AVERAGEX(values(Table[Supplier]),[Average_Aux])

[Anonymous]

Thank you for the reply 🙂

The average was working, but when I try to replicate to standard deviation:

StdDev_Aux = CALCULATE(STDEVX.P(Table,Table[LT]),ALLEXCEPT(Table,Table[Supplier],Table[Material Code],Table[Purchase Order Date])) ----->this one gives me the the standard deviation in the graph, but not per supplier

Then I applied your formula and it didn't work

StdDev= STDEVX.P(VALUES(Table[Supplier]),[StdDev_Aux])