Forum Discussion

kirbynguyen's avatar
kirbynguyen
Helper II
5 years ago
Solved

Standard Deviation DAX

Hello,   I am trying to calculate standard deviation from my data:   Product Profit Count Total 1 100 5 500 1 200 4 800 1 300 3 900 1 400 1 400 2 100 7 700 ...
Need Help
Show and Tell
  • kirbynguyen's avatar
    kirbynguyen
    5 years ago

    smpa01  Greg_Deckler 

     

    Well, I think I figured it out. Here's the solution:

     
    prodtop = POWER('Table'[Profit] - 'Table'[prodmean], 2) * 'Table'[Count]
    prodtoptotal = CALCULATE(SUM('Table'[prodtop]), ALLEXCEPT('Table', 'Table'[Product]))
    variance = 'Table'[prodtoptotal] / 'Table'[prodcount]
    std = SQRT('Table'[variance])
     
    I think I can consolidate this into fewer columns so I'll work on that. Thanks for the help 😁
smpa01's avatar
smpa01
Community Champion
5 years ago

kirbynguyen  were you hoping for this

_total:= CALCULATE(SUMX('Table 1','Table 1'[Total]),ALLEXCEPT('Table 1','Table 1'[Product]))
_count:= CALCULATE(SUMX('Table 1','Table 1'[Count]),ALLEXCEPT('Table 1','Table 1'[Product]))
_mean:= DIVIDE([_total],[_count])
_sq:= (MAX('Table 1'[Total])-[_mean])^2
_sumsq:= CALCULATE(SUMX('Table 1',[_sq]),ALLEXCEPT('Table 1','Table 1'[Product]))
STDEV:= SQRT(DIVIDE([_sumsq],[_count]))