Forum Discussion
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 |
| 2 | 200 | 3 | 600 |
Total = Profit * Count. I am trying to calculate the standard deviation per product using the Total column as the value and the Count column as the counts. I was able to create a calculated column for mean:
Mean = CALCULATE(SUM(total) / SUM(count), ALLEXCEPT(table, product))
The DAX for STDEV doesn't seem to work for me as I was getting an extemely large number. Please help me find the correct way to calculate the standard deviation.
Thanks in advance!
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 😁
8 Replies
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]))kirbynguyen What number are you expecting? The default Standard Deviation aggregation seems correct to me.
I've calculated the Standard Deviations by hand and I got 96.0769 for product 1 and 45.8258 for product 2.
But this is the result when I used the STDEV functions:
This is because it doesn't take into account the total counts and I couldn't figure out a way to include it.
kirbynguyencan you show the formula that you used to arrive on 96.0769 for product 1 and 45.8258 for product 2
