Forum Discussion
Power BI vs. Excel Standard Deviation Values Are Different
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.
5 Replies
- lbendlin
Super User
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 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
Community Support
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
