Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dynamic standard deviation - DAX

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 accordin...
Need Help
Tips and Tricks
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Anonymous , Try like

 

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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