Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 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 .. 😞
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 |
Solved! Go to Solution.
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.
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
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.
Hi @OzcarHui07,
Great to hear you have found the solution! Could you accept your reply above as solution to close this thread?
Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |