Power BI vs. Excel Standard Deviation Values Are Different

[Anonymous]

Hello,

I'm trying to make a report on Power BI and I need to find the standard deviation of a column according to the filters.

I made a small example of it on Excel and it worked properly there however, I cannot make the same calculation and reach the same result on Power BI.

As you can see, on excel, by using the stdev.p formula, the answer with the same values is 5902

On Power BI, same numbers, using the same formula, the answer comes as 1373. In my dataset, there are six months so I want it to be sensitive to the filters. That's why I used the following on Power BI.

Std. Dev. Stock (BG) = calculate(STDEV.P('Model'[Gün Sonu Reyon Stok Ortalaması]),ALLSELECTED())

I'm making a mistake somewhere and I am stuck. If you could help me out, I'd be grateful.

Thanks in advance and best regards,

[Anonymous]

Merhaba, 

Sorry for the lack of info above.  I'm a rookie when it comes to Power BI. I'll give the data in a table now.

Here's what I try to do, I try to calculate a single value for each month. Single average of categories, single std. deviation for each month. Unfortunately, when I try to use stdev.p formula in Power BI, the result is like the screenshot in the original message. Of course I want them to be sensitive to the filters that I will apply via slicers because there will be multiple countries etc. These values are for a single country. 

The formula I used for the calculation is below:

Std. Dev. STOCK (BG) = CALCULATE(STDEV.P('Model'[STOCK]),ALLEXCEPT('Model','Model'[CATEGORIES],'Model'[MONTH],'Model'[COUNTRY 1]) )

Unfortunately, I can't share a wider dataset due to company constraints. I hope this helps. Also sorry for my late reply and thanks a lot for your help in advance!

Best regards,

My dataset is below:

MONTH	CATEGORIES	STOCK
3	Category 1	20.342
3	Category 2	9.284
3	Category 4	17
3	Category 3	901
4	Category 1	19.190
4	Category 2	8.920
4	Category 4	7
4	Category 3	232
5	Category 1	17.207
5	Category 2	8.529
5	Category 4	6
5	Category 3	1.471
6	Category 1	15.219
6	Category 2	7.198
6	Category 4	12
6	Category 3	1.865

My expected outcomes are below:

Month	Average	Standard Deviation
3	7.636	8.179
4	7.087	7.857
5	6.803	6.816
6	6.074	5.902

[v-easonf-msft]

Hi, @Anonymous 

I have checked the formula below and the result is right.

Std. Dev. STOCK (BG) = STDEV.P('Model'[STOCK])

It is recommended to recheck your imported data to make sure there are no discrepancies in the data imported into powerbi.

Best Regards,
Community Support Team _ Eason

[Anonymous · Accepted solution]

Thank you so much for your solution! I don't get the same results on my own file so I'll actually try importing the data once more. As you said, there might be some issues with my file. Best regards!

[lbendlin]

Yes, your sample data has lots of leading spaces in the number columns. Might want to clean that up.

I tested with the actual sample data and both implicit and explicit measures work as expected.

[lbendlin]

Merhaba, Please provide sanitized sample data that fully covers your issue. I cannot help you without usable sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523