Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 the...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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!

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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:

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

 

My expected outcomes are below:

MonthAverageStandard Deviation
3        7.636        8.179
4        7.087        7.857
5        6.803        6.816
6        6.074        5.902

 

v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      lbendlin
      Icon for Super User rankSuper User
      4 years ago

      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.