Normal Distribution (Gauss Curve) in Power BI (Part I)
HTML-код
- Опубликовано: 11 сен 2024
- In this video I explain how to create a normal distribution curve from a given data.
Next videos will explain more details about how to create the probability and the number of population needed for certain error.
To download the file: 1drv.ms/f/s!Ar...
![MISSING: SLIM SHADY [Expanded Mourner’s Edition Trailer]](http://i.ytimg.com/vi/xh8qgHrOO1g/mqdefault.jpg)
Hi Josep, thank you so much for the excellent tutorial! One thing I added was a wrapper to the f(x) measure: IF(HASONEVALUE('Normal Distribution'[X]),,BLANK()). This protects against the crossfilter error, so you don't have to turn off the totals in the chart.
Hi Joseph.
GREAT!! thanks a lot for your contribution!!
You can also use the FIRSTNONBLANK('NormDist'[X],1) function instead of VALUES(), combined with the NORM.DIST() function to get at your f(x) value.
Thanks for sharing your knowledge. Very good video and illustration.
Excellent!!! Thanks very much for a great demonstration of Power BI capabilities.
Thanks a lot Lenzy!!
Have a nice year!!!
Fantastic video, this helped me deliver insights on our truck turn times to our CIO. Muchas Gracias!
Many thanks to you for watching!😀
I was facing problems because my bucking values were not small enough and the normal distribution (bell) curve was quite "square". I solved this using the formula for X:
NORMAL DISTRIBUTION =
VAR MinValue=FLOOR([X-3σ],1)
VAR MaxValue=CEILING([X+3σ],1)
RETURN
GENERATESERIES(MinValue,MaxValue,0.01)
Great!!
Thanks for sharing.
thank you so much!!!
Thank you for sharing your input! Very helpfull
Hello Joseph, your youtube video is very helpful. i was trying to follow your path to create a Gauss curve but got stuck at f(x).. my calculation is f(x) =
EXP((VALUES('NORMAL DISTRIBUTION'[X])-[MEAN]^2/(2*[STANDAR DEV.]^2)*-1)/SQRT((2*PI())*[STANDAR DEV.])) however i got an error message saying 'MdxScrip(Model) (8,6) Calculation error in measure 'NORMAL DISTRIBUTION'[f(x): A table of multiple values was supplied where a single value was expected.' I was under impression that multiple values should be displayed as there are multiple rolls? Also i turned off 'total' on x and z---Are you able to help?
Thanks a lot FNLN
Do you creat a column or a mesure?
Check this...
I have the same problem but I use another formula to correct it: f(x) = NORM.DIST(AVERAGE('NORMAL DISTRIBUTION'[X]), [MEAN (µ)], [STANDAR DEV. (σ)],FALSE())
Maybe this could help you. And thank you very much for the video which really helped me to draw the normal distribution.
Great tutorial, Josep - very helpful - thanks for posting!
Hi Douglas, thanks for watching!
Excellent work, very useful! Much appreciated! Thank you!
I'm glad to read this!!
Thanks a lot Ciprian!!!
Great Job!! and thank you for your help. From Ecuador.
Hi Alejandro, pleased to help you!
Thank you so much . It is what I was looking for.
You're welcome!!
Glad to help you!
Hola Josep,
No se si quizas sea que la version de PowerBI ha cambiado (trabajo con el programa en ingles en USA - los formats a lo mejor son distintos) pero la ultima formula me da un error que se soluciona con la funcion FIRSTNONBLANK(x,1) de esta forma:
f(x) =
EXP((FIRSTNONBLANK('NORMAL DISTRIBUTION - CHK'[X],1)-[Mean])^2/(2*[Standard Dev]^2)*-1)/(SQRT(2*PI())*[Standard Dev])
Ojala le sirva a alguien. Muchisimas gracias por el contenido.
Thanks ! that helped me a ton!!
@@nilmuriasfalgas3602 glad to hear of my friend!
Muchas gracias por el aporte. A mi también me salía error.
@@kikesaavedra1316 me alegro haber ayudado!
Great video, super helpful! Thanks!
i really love this, THANKS!! for sharing
👍
Thanks a lot. Highly appreciated.
Thans to you, Ashim!!
Excellent video, thanks for sharing.
This is awesome, thanks so much!!!
Hi Josep - love the video! However, I have one question for you. Is there a way to have the 'Normal Distribution" table that calculates values for X +/- 3 standard deviations be dynamic based on an underlying filter? For instance, the table works as described in your video when I am looking at the entire data set that I have, which spans 5 years. However, when I apply a slicer to only look at 1 year of data (instead of 5 years) the Normal Distribution table does not adjust accordingly, and thus the mean and +/- 1 standard deviation bars do not reflect properly in the graph. This is because the Normal Distribution table is simply taking the max and min from the data set regardless of any dates, etc. I can get all the measures to reflect properly using the slicer, with exception of the bars for the mean and +/-1 1 standard deviation on the chart. Any insight you have on this would be appreciated!
Hi John, sorry for delay!!
I understand the problem, but still not know how to solve it!
The problem appears when X is calculated by the formula:
NORMAL DISTRIBUTION =
VAR MInValue=FLOOR([X-3σ];1)
VAR MaxValue=CEILING([X+3σ];1)
RETURN
SELECTCOLUMNS(CALENDAR(MInValue;MaxValue);"X";INT([Date]))
This formula does not take in mind the filters, probably because it is a formula to create calendars, not other kind of columns.
I will investigate about this for an alternative solution.
Thanks a lot!
Josep.
@@josepromero1452 Hello Josep ! Thank you for the video ! I really loved it! It helped me a lot. But as mentionned John, it is not dynamic. Did you find an answer about that problem ? I imagined to change calendar to something else (values or sample for examples) but it didn't work.
Also, on the version of August, NORM.DIST can be used. But still X need to be created (or I am really not good in Power BI). DId you tried this formula ?
Thanks again and hope you can help me again !
Ophélie
@@josepromero1452 Hello ! In my company, we found a solution to this problem ! in fact it is on the writing of NORMAL Z VALUE. the formula to use is : round(([slicer value]-[average (µ)])/[standard dev. (σ)];2)
Then if you want also µ, µ-1 and µ+1 dynamic. You have to create them as measure like this : µ=if('NORMAL DISTRIBUTION'[X]=floor(Average(µ)];1);[f(x)];blank())
µ-1=if(selectdvalue('normal distribution'[X])=floor([X-1σ];1);[f(x)];blank())
@@ophelietab5112 I have the same problem. I saw your code, but have some trouble understanding it. How did you define the [slicer value] to put in the calculation of Z? And does Z need to be a measurement or a column? Is it possible that you sent me an example file?
@@steefcreemers6079 Hello Steef, I don't remember exactly. I left that company and I am not using power BI anymore. But I did exactly like in the video instead what I just described
excellent video! Thank you so much!
Thanks for watching!
Thanks for these valuabes insights!
Many Thanks !! 👍
Hello, I adapted this normal distribution to a data separated by date, but the Z value only calculates the whole set, so when I select a year, it doesnt change, but the f(x) change, and all other parameters change, like the average and standard deviation. Since the Z value depends on X, average and standard deviation, it should change too, but it doesnt happen. Can you help me fix this?
Enjoyed your video!
Would it be possible to re-share your pbix file?
Josep, I don't understand why there is Date involve on the table Distribution Normal ? I don't see you have Date table as well.
Hi Feng,
it is the only way I found to abtain a table with consecutive values where minimum and maximum are known. As you can see, nothing involved with dates...
Hi @@josepromero1452, there is GENERATESERIES for the purpose, but your way does the trick : )
Great video.
Thank you Josh!!
Hi Josep, Thanks for sharing this very helpful tutorial.
However I also have the same problem as Gloria M below, when I'm trying to print the F(x) in the table, I get the "MdxScript(Model) (16, 41) Calculation error in measure 'Normal distribution'[f(x)]: A table of multiple values was supplied where a single value was expected" error message. I did check if I use the new Measure or Colum method.. and I do use Measure. Can you advice?
Hello Lars,
thanks a lot!.
Try to disable totals in the table format painter.
This error is very common, mostly times solved with this solution.
Regards!
Josep.
@@josepromero1452 That worked, Thanks
This worked for me too! Should have read the comments before wasting 30 mins in google :(
Also, could have just kept playing your tutorial...
I am receiving the following error "An argument of function "CEILING' has the wrong data type or the result is too large or too small"
NormalDistribution =
VAR Minvalue = FLOOR([X-3dev],1)
VAR Maxvalue = CEILING([X+3dev],1)
RETURN
SELECTCOLUMNS(CALENDAR(Minvalue,maxvalue),"A",INT([fldCallDate]))
Please steer me in the right direction
Thanks
Hi Josep, thanks for sharing this! it's helpful! Question: when creating your normal distribution in a new table, why do you use Calendar function? What do I do if my real data value has decimal?
Hello Ashley,
The only way to create a table with the unique values of the data is with the function CALENDAR, at least, is the only way I found.
If you have decimal values on your data, transform them into integer values multiplying them by a multiple of 10 on your query (e.g. if you have 3 decimals, multiply by 1000), and to show in the dashboard, divide the result of the formulas by the same multiplier.
I hope it helps you.
Regards,
Josep.
@@josepromero1452 : You can use the what if parameter. It uses the generateseries function. (Parameter = GENERATESERIES(-20, 20, 1))It also creates a special kind of slicer
@@krisvandeurzen5507 Thanks for sharing!!
GRacias Josep. Muy buen video.
Quería hacerte una pregunta.
Cuando pongo la fórmula para f(x), me da un error en ´NORMAL DISTRIBUTION´[x]-[MEAN (u)]. Pongo u porque no sé poner en el teclado el símbolo correcto, pero tengo bien escrita esa parte.
Me sale un error que doce que no se puede determinar un valor único para la columna ´x´y dice que esto puede suceder cuando una fórmula de medida hace referencia a una columna con muchos valores sin especificar mínimo, máximo...
Los datos ´X´de mi tabla realmente van del 18000 al 50000 pero entiendo que esto no debiera influir.
¿Podrías ayudarme o indicarme algún correo o similar para contactar contigo?
GRacias de nuevo¡¡¡
Hola Arturo, la letra µ se escribe como ALT+230
En la fórmula, debes añadir VALUES antes de la columna [X]. Comprueba que en la columna [X] tienes valores únicos, enteros y sucesivos. Comprueba también que todos los paréntesis están en su posición correcta. Si la fórmula está bien escrita, no debería darte problemas.
Un saludo,
Josep.
@@josepromero1452 "Correcto, gracias de nuevo, y más si cabe por la rapidez. Ya lo he hecho, y me acepta la fórmula, pero aunque he desactivado TOTALES, no se me incluyen esos valores en la nueva tabla. Me dice que "un argumento de la función POWER tiene un tipo de datos incorrecto, o el resultado es demasiado grande o demasiado pequeño"
nada¡¡¡ ya he visto el fallo. me faltaba un ( y por eso daba un valor que no reconocía. lo voy a comprobar
This is so awesome ! congrats. Could you please share the excel files ?
Thanks for the video! Could you share how to do Standard deviation with not integer numbers with 1 digit after the decimal?
Me indica este error al formular F(x)
A table of multiple values was supplied where a single value was expected.
La fórmula es correcta:
F(x) =
EXP((VALUES('Normal Distribution_Lines'[X])-[AVG_Lineas])^2/(2* Medidas[Desv.Lineas]^2)*-1)/(SQRT(2*PI())* Medidas[Desv.Lineas])
Intenta desactivar los totales en la tabla
Hello, where can we find the word document you showed detailing the bell curve?
Hello Paul.
Here it is:
es.slideshare.net/EdgardoMolinaOchoa/distribucion-normal-modelos-de-probabilidad-continuos
Thanks for watching!!
Nice!
Thanks a lot!
Josep
Great!
Thanks a lot Renato!!
sir when table for NORMAL DISTRIBUTION IS CREATED,IT SHOWS SYNTAX ERRORS WHEN I TRIED TO WRITE "VAR"
Hi Nabb,
please, download my file on the description link and check if it works, if so, compare with yours.
The sentence VAR should work always and it is pretty useful for many DAX instructions.
Have a nice New Year!!!
Cordial saludo Josep, hasta ahora estoy aprendiendo sobre power BI, que pagina en español, me puede recomendar para aprender mas sobre este gran tema.
gracias,
Hola José Julián,
desgraciadamente hay pocas publicaciones decentes sobre este tema en español...
Un saludo,
Josep.
The syntax for ';' is incorrect. (DAX(VAR MInValue=FLOOR([X-3σ];1)VAR MaxValue=CEILING([X+3σ];1)RETURNSELECTCOLUMNS(CALENDAR( ...this is showing error when i tried creating the new table of normal distribution
Hi Nabb!
Probably you are using an English version of PBI. Please, try to change the ";" for ","
Hope it helps you.
Please let me know if this solves the error.
Josep.
@@josepromero1452Doing this solves the issue. Thanks for the tutorial.
another way maybe: f(x) = NORM.DIST('NORMAL DISTRIBUTION'[X],[MEAN (μ) ],[STD.DEV (σ) ],0)
Thats what I was Looking for
O excel baixado, não está igual ao exibido no vídeo, pode atualizar ?
Hi Sidney,
Follow Parts II and III videos.
Regards!!
I can t find the file with the colestoral data, somebody can help me?
Hello Felipe, you can find both files in the link below description.
@@josepromero1452 Hello Sir , when I check OneDrive folder , I found only 'Distribution normal' excel file, not included 'Diabetes' file that needed to input as you teach. Kindly let me know where can I find that file. Thanks for sharing and sorry for disturbing you.
@@thetpaingmyo9077 Hello
Thet Paing Myo, now it is ready to download. Thanks for the info!
Sería de mucha ayuda si subes el tutorial en español
Gracias por tu comentario, lo tendré en cuenta para próximos videos.
Thanks for the video！Will u do a further video to explain how to draw two samples t test？