Reply
Frequent Visitor
Posts: 7
Registered: ‎03-11-2017
Accepted Solution

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:

 

2.PNG

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

 

1.PNG

 

The summation is:  Sum(FactVentas[CantidadSolicitada]) = 6.029.814,50

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 .. Smiley Sad

 

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:

 

FechaCantidadSolicitada
29/01/2016 0:005400
13/02/2016 0:0025370
14/02/2016 0:0018354
15/02/2016 0:0037673,07
16/02/2016 0:0040386,07
01/06/2016 0:0030241
02/06/2016 0:0030840
03/06/2016 0:0044288,47
04/06/2016 0:0036460,6
05/06/2016 0:0025374,47
06/06/2016 0:0032789,54
07/06/2016 0:0033500
08/06/2016 0:0044150,73
09/06/2016 0:0026053,77
10/06/2016 0:0042985,96
11/06/2016 0:0034307,07
12/06/2016 0:0034146,77
13/06/2016 0:0015800
15/06/2016 0:001200
17/06/2016 0:0046624,59
18/06/2016 0:0034708,07
19/06/2016 0:0019155,07
20/06/2016 0:0034472
21/06/2016 0:0021476,07
22/06/2016 0:0036010,07
23/06/2016 0:0041582,54
24/06/2016 0:0047069,84
25/06/2016 0:0049396,25
26/06/2016 0:0038114,55
27/06/2016 0:0044904,97
28/06/2016 0:0040014,47
29/06/2016 0:0058434,44
30/06/2016 0:0044645,46
01/07/2016 0:0062832,4
02/07/2016 0:0066017,97
03/07/2016 0:0015055
04/07/2016 0:0052897,05
05/07/2016 0:0047992
06/07/2016 0:0053885,46
07/07/2016 0:0026107,94
08/07/2016 0:0025988
09/07/2016 0:0031059,88
10/07/2016 0:0031424,93
11/07/2016 0:0048769,43
12/07/2016 0:0040343
13/07/2016 0:0042354
14/07/2016 0:0040666,38
15/07/2016 0:0044493,51
16/07/2016 0:0034003
17/07/2016 0:0035709,07
18/07/2016 0:0026816,07
19/07/2016 0:0019595
20/07/2016 0:0026214
21/07/2016 0:0047388
22/07/2016 0:0035360,55
23/07/2016 0:0032257,57
24/07/2016 0:0028955,57
25/07/2016 0:0048427,57
26/07/2016 0:0018033,57
27/07/2016 0:0026718,45
28/07/2016 0:0020057,47
29/07/2016 0:0014187
30/07/2016 0:0032942
31/07/2016 0:0029402,47
01/08/2016 0:0019836
02/08/2016 0:0032403,47
03/08/2016 0:0030450
04/08/2016 0:0038765,36
05/08/2016 0:0038466,47
06/08/2016 0:0033007,47
07/08/2016 0:0029349,47
08/08/2016 0:0028046,48
09/08/2016 0:0034030,3
10/08/2016 0:0035404,47
11/08/2016 0:0032650,47
12/08/2016 0:0038201,76
13/08/2016 0:0037843,07
14/08/2016 0:0026245,07
15/08/2016 0:0027906,05
16/08/2016 0:0036777,13
17/08/2016 0:0042458,13
18/08/2016 0:0036518,32
19/08/2016 0:0015917,07
20/08/2016 0:0041131,14
21/08/2016 0:0027198,29
22/08/2016 0:0038629,07
23/08/2016 0:0035909
24/08/2016 0:0043038,07
25/08/2016 0:0025752,87
26/08/2016 0:0057204,32
27/08/2016 0:0028020,08
28/08/2016 0:0031792,54
29/08/2016 0:0044707
30/08/2016 0:0024270
31/08/2016 0:0048509,08
01/09/2016 0:008250
02/09/2016 0:005000
03/09/2016 0:0015973,08
04/09/2016 0:00700
05/09/2016 0:006100
06/09/2016 0:0014434,07
09/09/2016 0:009900
10/09/2016 0:0015474,07
11/12/2016 0:005000
16/12/2016 0:001000
19/12/2016 0:0018900
20/12/2016 0:0012220
21/12/2016 0:0012745,67
22/12/2016 0:003000
23/12/2016 0:001000
24/12/2016 0:002900
25/12/2016 0:001000
26/12/2016 0:0011340
27/12/2016 0:0025978,75
28/12/2016 0:0031099,6
29/12/2016 0:0017519,21
30/12/2016 0:0028267,67
31/12/2016 0:0014101,59
01/01/2017 0:0013000
02/01/2017 0:0033484,21
03/01/2017 0:0029586,74
04/01/2017 0:0018001,59
05/01/2017 0:0028604,07
06/01/2017 0:0027952,09
07/01/2017 0:0027883,22
08/01/2017 0:0010600
09/01/2017 0:0020183,22
10/01/2017 0:0028130,73
11/01/2017 0:0022554,07
12/01/2017 0:0015300
13/01/2017 0:0018401,59
14/01/2017 0:0026525,27
15/01/2017 0:0018146,52
16/01/2017 0:0022396,52
17/01/2017 0:0032911,47
18/01/2017 0:0035051,59
19/01/2017 0:0015454,07
20/01/2017 0:0030743,45
21/01/2017 0:0022230,73
22/01/2017 0:0014300
23/01/2017 0:0026690,27
24/01/2017 0:0026029,15
25/01/2017 0:0045341,16
26/01/2017 0:0029904,07
27/01/2017 0:0039091,16
28/01/2017 0:0028961,16
29/01/2017 0:007903,18
30/01/2017 0:0048490,91
31/01/2017 0:0032850
01/02/2017 0:0045734,21
02/02/2017 0:0038803,18
03/02/2017 0:0036351,59
04/02/2017 0:0034329,74
05/02/2017 0:0018676,27
06/02/2017 0:0031113,84
07/02/2017 0:0027550
08/02/2017 0:0034473,25
09/02/2017 0:0039392,39
10/02/2017 0:0028703,18
11/02/2017 0:0025236,35
12/02/2017 0:0015436,35
13/02/2017 0:0031586,35
14/02/2017 0:0035840,71
15/02/2017 0:0038726,8
16/02/2017 0:0028195,63
17/02/2017 0:0049824,04
18/02/2017 0:0028091,61
19/02/2017 0:0025574,6
20/02/2017 0:0029618,27
21/02/2017 0:0029849,6
22/02/2017 0:0022860,97
23/02/2017 0:0023914,15
24/02/2017 0:0049206,46
25/02/2017 0:0042802,44
26/02/2017 0:0032236,74
27/02/2017 0:0042132,87
28/02/2017 0:0011977,06
01/03/2017 0:0048666,78
02/03/2017 0:0039866,64
03/03/2017 0:0016181,23
04/03/2017 0:006593,35
05/03/2017 0:0037412,02
06/03/2017 0:0025643,3
07/03/2017 0:0032066,58
08/03/2017 0:0017859,15
09/03/2017 0:0012407,74
10/03/2017 0:0012599,6
11/03/2017 0:0019086,05
12/03/2017 0:0031111,12
13/03/2017 0:0052020,46
14/03/2017 0:0027566,88
15/03/2017 0:0045870,06
16/03/2017 0:0024318,47
17/03/2017 0:007087,49
18/03/2017 0:0029269,36
19/03/2017 0:0021819,36
20/03/2017 0:001500
21/03/2017 0:0014386,79
22/03/2017 0:0025387,09
23/03/2017 0:0016473,58
24/03/2017 0:004199,6
25/03/2017 0:007000
26/03/2017 0:001000
27/03/2017 0:003000
28/03/2017 0:0033238,13
29/03/2017 0:001000
01/04/2017 0:0028318,97
02/04/2017 0:0033318,97
03/04/2017 0:0041539,72
04/04/2017 0:0024719,36
05/04/2017 0:0031119,82
06/04/2017 0:004500
07/04/2017 0:004000
08/04/2017 0:001000
09/04/2017 0:001000
10/04/2017 0:001000
22/06/2017 0:005500

Accepted Solutions
Frequent Visitor
Posts: 7
Registered: ‎03-11-2017

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

Hi @v-ljerr-msft

 

Yes, thanks por your reply.


Really, i have analyticed more about this data
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.

View solution in original post


All Replies
Super Contributor
Posts: 3,697
Registered: ‎07-17-2016

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. Smiley Happy

 

e1.PNG

 

m1.PNGm2.PNG

 

Regards

Frequent Visitor
Posts: 7
Registered: ‎03-11-2017

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

Hi @v-ljerr-msft

 

Yes, thanks por your reply.


Really, i have analyticed more about this data
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.

Super Contributor
Posts: 3,697
Registered: ‎07-17-2016

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? Smiley Happy

 

Regards