cancel
Showing results for
Did you mean:
Frequent Visitor

## How to find a vector for the standard deviation, the formula of STDEVP and STDEV.P does not help

Good morning to all
I am new using the Power BI tool, I want you to help me how to properly apply the standard deviation formula from the available data that are on the bottom.

I worked the same data in a table and the results are:

So far good, it is a little complicated to prove the correct values in power bi .. at the time of making the standard deviation:

The average per day is:   CALCULATE(DIVIDE([CantSol];[Numero de Dias])) = 27.787,16

Standard deviation - statistical formula is:  SUM( (Vector(x) - Promedio(x))^2) / (N -1)

And when I apply the DAX formula of power BI: STDEV.P or STDEVX.P the results are not equivalent to those of Excel:
According to excel the deviation is: 13,898.9
According to power bi desc st is: 996,68
They should be the same ..

I know that in order to find this formula, it is necessary to obtain the entire vector of the requested quantity
Any solution alternative? I need help.

Thank you very much.

DATA:

 Fecha CantidadSolicitada 29/01/2016 0:00 5400 13/02/2016 0:00 25370 14/02/2016 0:00 18354 15/02/2016 0:00 37673,07 16/02/2016 0:00 40386,07 01/06/2016 0:00 30241 02/06/2016 0:00 30840 03/06/2016 0:00 44288,47 04/06/2016 0:00 36460,6 05/06/2016 0:00 25374,47 06/06/2016 0:00 32789,54 07/06/2016 0:00 33500 08/06/2016 0:00 44150,73 09/06/2016 0:00 26053,77 10/06/2016 0:00 42985,96 11/06/2016 0:00 34307,07 12/06/2016 0:00 34146,77 13/06/2016 0:00 15800 15/06/2016 0:00 1200 17/06/2016 0:00 46624,59 18/06/2016 0:00 34708,07 19/06/2016 0:00 19155,07 20/06/2016 0:00 34472 21/06/2016 0:00 21476,07 22/06/2016 0:00 36010,07 23/06/2016 0:00 41582,54 24/06/2016 0:00 47069,84 25/06/2016 0:00 49396,25 26/06/2016 0:00 38114,55 27/06/2016 0:00 44904,97 28/06/2016 0:00 40014,47 29/06/2016 0:00 58434,44 30/06/2016 0:00 44645,46 01/07/2016 0:00 62832,4 02/07/2016 0:00 66017,97 03/07/2016 0:00 15055 04/07/2016 0:00 52897,05 05/07/2016 0:00 47992 06/07/2016 0:00 53885,46 07/07/2016 0:00 26107,94 08/07/2016 0:00 25988 09/07/2016 0:00 31059,88 10/07/2016 0:00 31424,93 11/07/2016 0:00 48769,43 12/07/2016 0:00 40343 13/07/2016 0:00 42354 14/07/2016 0:00 40666,38 15/07/2016 0:00 44493,51 16/07/2016 0:00 34003 17/07/2016 0:00 35709,07 18/07/2016 0:00 26816,07 19/07/2016 0:00 19595 20/07/2016 0:00 26214 21/07/2016 0:00 47388 22/07/2016 0:00 35360,55 23/07/2016 0:00 32257,57 24/07/2016 0:00 28955,57 25/07/2016 0:00 48427,57 26/07/2016 0:00 18033,57 27/07/2016 0:00 26718,45 28/07/2016 0:00 20057,47 29/07/2016 0:00 14187 30/07/2016 0:00 32942 31/07/2016 0:00 29402,47 01/08/2016 0:00 19836 02/08/2016 0:00 32403,47 03/08/2016 0:00 30450 04/08/2016 0:00 38765,36 05/08/2016 0:00 38466,47 06/08/2016 0:00 33007,47 07/08/2016 0:00 29349,47 08/08/2016 0:00 28046,48 09/08/2016 0:00 34030,3 10/08/2016 0:00 35404,47 11/08/2016 0:00 32650,47 12/08/2016 0:00 38201,76 13/08/2016 0:00 37843,07 14/08/2016 0:00 26245,07 15/08/2016 0:00 27906,05 16/08/2016 0:00 36777,13 17/08/2016 0:00 42458,13 18/08/2016 0:00 36518,32 19/08/2016 0:00 15917,07 20/08/2016 0:00 41131,14 21/08/2016 0:00 27198,29 22/08/2016 0:00 38629,07 23/08/2016 0:00 35909 24/08/2016 0:00 43038,07 25/08/2016 0:00 25752,87 26/08/2016 0:00 57204,32 27/08/2016 0:00 28020,08 28/08/2016 0:00 31792,54 29/08/2016 0:00 44707 30/08/2016 0:00 24270 31/08/2016 0:00 48509,08 01/09/2016 0:00 8250 02/09/2016 0:00 5000 03/09/2016 0:00 15973,08 04/09/2016 0:00 700 05/09/2016 0:00 6100 06/09/2016 0:00 14434,07 09/09/2016 0:00 9900 10/09/2016 0:00 15474,07 11/12/2016 0:00 5000 16/12/2016 0:00 1000 19/12/2016 0:00 18900 20/12/2016 0:00 12220 21/12/2016 0:00 12745,67 22/12/2016 0:00 3000 23/12/2016 0:00 1000 24/12/2016 0:00 2900 25/12/2016 0:00 1000 26/12/2016 0:00 11340 27/12/2016 0:00 25978,75 28/12/2016 0:00 31099,6 29/12/2016 0:00 17519,21 30/12/2016 0:00 28267,67 31/12/2016 0:00 14101,59 01/01/2017 0:00 13000 02/01/2017 0:00 33484,21 03/01/2017 0:00 29586,74 04/01/2017 0:00 18001,59 05/01/2017 0:00 28604,07 06/01/2017 0:00 27952,09 07/01/2017 0:00 27883,22 08/01/2017 0:00 10600 09/01/2017 0:00 20183,22 10/01/2017 0:00 28130,73 11/01/2017 0:00 22554,07 12/01/2017 0:00 15300 13/01/2017 0:00 18401,59 14/01/2017 0:00 26525,27 15/01/2017 0:00 18146,52 16/01/2017 0:00 22396,52 17/01/2017 0:00 32911,47 18/01/2017 0:00 35051,59 19/01/2017 0:00 15454,07 20/01/2017 0:00 30743,45 21/01/2017 0:00 22230,73 22/01/2017 0:00 14300 23/01/2017 0:00 26690,27 24/01/2017 0:00 26029,15 25/01/2017 0:00 45341,16 26/01/2017 0:00 29904,07 27/01/2017 0:00 39091,16 28/01/2017 0:00 28961,16 29/01/2017 0:00 7903,18 30/01/2017 0:00 48490,91 31/01/2017 0:00 32850 01/02/2017 0:00 45734,21 02/02/2017 0:00 38803,18 03/02/2017 0:00 36351,59 04/02/2017 0:00 34329,74 05/02/2017 0:00 18676,27 06/02/2017 0:00 31113,84 07/02/2017 0:00 27550 08/02/2017 0:00 34473,25 09/02/2017 0:00 39392,39 10/02/2017 0:00 28703,18 11/02/2017 0:00 25236,35 12/02/2017 0:00 15436,35 13/02/2017 0:00 31586,35 14/02/2017 0:00 35840,71 15/02/2017 0:00 38726,8 16/02/2017 0:00 28195,63 17/02/2017 0:00 49824,04 18/02/2017 0:00 28091,61 19/02/2017 0:00 25574,6 20/02/2017 0:00 29618,27 21/02/2017 0:00 29849,6 22/02/2017 0:00 22860,97 23/02/2017 0:00 23914,15 24/02/2017 0:00 49206,46 25/02/2017 0:00 42802,44 26/02/2017 0:00 32236,74 27/02/2017 0:00 42132,87 28/02/2017 0:00 11977,06 01/03/2017 0:00 48666,78 02/03/2017 0:00 39866,64 03/03/2017 0:00 16181,23 04/03/2017 0:00 6593,35 05/03/2017 0:00 37412,02 06/03/2017 0:00 25643,3 07/03/2017 0:00 32066,58 08/03/2017 0:00 17859,15 09/03/2017 0:00 12407,74 10/03/2017 0:00 12599,6 11/03/2017 0:00 19086,05 12/03/2017 0:00 31111,12 13/03/2017 0:00 52020,46 14/03/2017 0:00 27566,88 15/03/2017 0:00 45870,06 16/03/2017 0:00 24318,47 17/03/2017 0:00 7087,49 18/03/2017 0:00 29269,36 19/03/2017 0:00 21819,36 20/03/2017 0:00 1500 21/03/2017 0:00 14386,79 22/03/2017 0:00 25387,09 23/03/2017 0:00 16473,58 24/03/2017 0:00 4199,6 25/03/2017 0:00 7000 26/03/2017 0:00 1000 27/03/2017 0:00 3000 28/03/2017 0:00 33238,13 29/03/2017 0:00 1000 01/04/2017 0:00 28318,97 02/04/2017 0:00 33318,97 03/04/2017 0:00 41539,72 04/04/2017 0:00 24719,36 05/04/2017 0:00 31119,82 06/04/2017 0:00 4500 07/04/2017 0:00 4000 08/04/2017 0:00 1000 09/04/2017 0:00 1000 10/04/2017 0:00 1000 22/06/2017 0:00 5500
1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: How to find a vector for the standard deviation, the formula of STDEVP and STDEV.P does not help

and i believe that is true the results of Power BI.

The problems was the fact table of to sell had as 4000 rows and the results of formules STDEV.P or STDEVX.P are based in 4000 rows, and not in 217 days' date.

Then:
According to excel the deviation is: Vector(x) / 217 (of date days )= 13,898.9
According to power bi desc st is: Vector(x)/ 4798 (rows) = 996,68

It was complicate to do in the same fact table, beacuse it have dimensions Type Customer and Calendar.
Only i have created other fact table with data of Date by sum( Requested Quantity) with filter type customer as solutions and the run is perfect.

thanks, good day.

3 REPLIES 3
Super Contributor

## Re: How to find a vector for the standard deviation, the formula of STDEVP and STDEV.P does not help

Hi @OzcarHui07,

Based on my test with your shared sample data, using STDEV.P and STDEV.S Function (DAX) in Power BI Desktop will get same result as you provided in Excel.

Regards

Frequent Visitor

## Re: How to find a vector for the standard deviation, the formula of STDEVP and STDEV.P does not help

and i believe that is true the results of Power BI.

The problems was the fact table of to sell had as 4000 rows and the results of formules STDEV.P or STDEVX.P are based in 4000 rows, and not in 217 days' date.

Then:
According to excel the deviation is: Vector(x) / 217 (of date days )= 13,898.9
According to power bi desc st is: Vector(x)/ 4798 (rows) = 996,68

It was complicate to do in the same fact table, beacuse it have dimensions Type Customer and Calendar.
Only i have created other fact table with data of Date by sum( Requested Quantity) with filter type customer as solutions and the run is perfect.

thanks, good day.

Highlighted
Super Contributor

## Re: How to find a vector for the standard deviation, the formula of STDEVP and STDEV.P does not help

Hi @OzcarHui07,

Great to hear you have found the solution! Could you accept your reply above as solution to close this thread?

Regards

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 108 members 1,523 guests
Recent signins: