Standard Deviation DAX

[kirbynguyen]

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!

[Greg_Deckler]

@kirbynguyen What number are you expecting? The default Standard Deviation aggregation seems correct to me.

[kirbynguyen]

@Greg_Deckler 

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.

[smpa01]

@kirbynguyencan you show the formula that you used to arrive on 96.0769 for product 1 and 45.8258 for product 2

[kirbynguyen]

@smpa01 

https://www.calculator.net/standard-deviation-calculator.html?numberinputs=100%2C100%2C100%2C100%2C1...

https://www.calculator.net/standard-deviation-calculator.html?numberinputs=100%2C100%2C100%2C100%2C1... 

Okay, I didn't do it by hand, but I plugged the numbers into a calculator 😛

[smpa01]

@kirbynguyen  DAX is working as expected

once your dataset is like this

[kirbynguyen]

@smpa01 

Well, the dataset was just a sample. I don't want to pivot my data since the counts are very high and there are more filters than just "product". Thanks for the input though!

[kirbynguyen · Accepted solution]

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

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