Normal Distribution (Gauss Curve) in Power BI (Part I)

No video

Normal Distribution (Gauss Curve) in Power BI (Part I)

Рет қаралды 47,883

Күн бұрын

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

Пікірлер: 94

@rudnickj 5 жыл бұрын

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.

@josepromero1452 5 жыл бұрын

Hi Joseph. GREAT!! thanks a lot for your contribution!!

@shanc77 4 жыл бұрын

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.

@nadersheikh00 5 ай бұрын

Thanks for sharing your knowledge. Very good video and illustration.

@lenzypetty1097 5 жыл бұрын

Excellent!!! Thanks very much for a great demonstration of Power BI capabilities.

@josepromero1452 5 жыл бұрын

Thanks a lot Lenzy!! Have a nice year!!!

@erickboczar678 3 жыл бұрын

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)

@josepromero1452 3 жыл бұрын

Great!! Thanks for sharing.

@meghk5147 2 жыл бұрын

thank you so much!!!

@ssergi0 Жыл бұрын

Thank you for sharing your input! Very helpfull

@ElBarbie23 Жыл бұрын

Fantastic video, this helped me deliver insights on our truck turn times to our CIO. Muchas Gracias!

@josepromero1452 Жыл бұрын

Many thanks to you for watching!😀

@stevensonvas 4 жыл бұрын

Great tutorial, Josep - very helpful - thanks for posting!

@josepromero1452 4 жыл бұрын

Hi Douglas, thanks for watching!

@InconsistentBastard 4 жыл бұрын

Excellent work, very useful! Much appreciated! Thank you!

@josepromero1452 4 жыл бұрын

I'm glad to read this!! Thanks a lot Ciprian!!!

@alejandroproano3260 5 жыл бұрын

Great Job!! and thank you for your help. From Ecuador.

@josepromero1452 5 жыл бұрын

Hi Alejandro, pleased to help you!

@gm.7458 5 жыл бұрын

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?

@josepromero1452 4 жыл бұрын

Thanks a lot FNLN Do you creat a column or a mesure? Check this...

@thierryfischer207 3 жыл бұрын

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.

@mohammedaminelachhabe2087 6 жыл бұрын

Thank you so much . It is what I was looking for.

@josepromero1452 6 жыл бұрын

You're welcome!! Glad to help you!

@davidt21 Жыл бұрын

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.

@nilmuriasfalgas3602 Жыл бұрын

Thanks ! that helped me a ton!!

@davidt21 Жыл бұрын

@@nilmuriasfalgas3602 glad to hear of my friend!

@kikesaavedra1316 3 ай бұрын

Muchas gracias por el aporte. A mi también me salía error.

@davidt21 3 ай бұрын

@@kikesaavedra1316 me alegro haber ayudado!

@TheAlbinoApple 3 жыл бұрын

Great video, super helpful! Thanks!

@yannkitson116 Жыл бұрын

Excellent video, thanks for sharing.

@haroudcuriel9431 3 жыл бұрын

i really love this, THANKS!! for sharing

@josepromero1452 3 жыл бұрын

👍

@ashim2019 6 жыл бұрын

Thanks a lot. Highly appreciated.

@josepromero1452 5 жыл бұрын

Thans to you, Ashim!!

@johndavidlearoyd3920 6 жыл бұрын

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!

@josepromero1452 6 жыл бұрын

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.

@ophelietab5112 5 жыл бұрын

@@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

@ophelietab5112 5 жыл бұрын

@@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())

@steefcreemers6079 5 жыл бұрын

@@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?

@ophelietab5112 5 жыл бұрын

@@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

@tinylittleanj2 4 жыл бұрын

This is awesome, thanks so much!!!

@feng3625 3 жыл бұрын

excellent video! Thank you so much!

@josepromero1452 3 жыл бұрын

Thanks for watching!

@lape36 3 жыл бұрын

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?

@josepromero1452 3 жыл бұрын

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.

@lape36 3 жыл бұрын

@@josepromero1452 That worked, Thanks

@meghk5147 2 жыл бұрын

This worked for me too! Should have read the comments before wasting 30 mins in google :(

@meghk5147 2 жыл бұрын

Also, could have just kept playing your tutorial...

@ashleyzhang8869 3 жыл бұрын

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?

@josepromero1452 3 жыл бұрын

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.

@krisvandeurzen5507 3 жыл бұрын

@@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

@josepromero1452 3 жыл бұрын

@@krisvandeurzen5507 Thanks for sharing!!

@andrekroker420 2 жыл бұрын

Thanks for these valuabes insights!

@josepromero1452 2 жыл бұрын

Many Thanks !! 👍

@becker1030 2 жыл бұрын

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?

@jgeorge-jx1gj Жыл бұрын

Enjoyed your video! Would it be possible to re-share your pbix file?

@gonzalorovito-operationsdi3921 2 жыл бұрын

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])

@josepromero1452 2 жыл бұрын

Intenta desactivar los totales en la tabla

@alessandrocanova363 4 жыл бұрын

This is so awesome ! congrats. Could you please share the excel files ?

@feng3625 3 жыл бұрын

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.

@josepromero1452 3 жыл бұрын

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

@dobrodziej 3 жыл бұрын

Hi @@josepromero1452, there is GENERATESERIES for the purpose, but your way does the trick : )

@kimdunbar6632 4 жыл бұрын

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

@olegkazanskyi9752 4 жыл бұрын

Thanks for the video! Could you share how to do Standard deviation with not integer numbers with 1 digit after the decimal?

@Extjac 5 жыл бұрын

another way maybe: f(x) = NORM.DIST('NORMAL DISTRIBUTION'[X],[MEAN (μ) ],[STD.DEV (σ) ],0)

@garypiper6010 2 жыл бұрын

Thats what I was Looking for

@_Jay4109 4 жыл бұрын

Great video.

@josepromero1452 4 жыл бұрын

Thank you Josh!!

@arturogonzalezgoni711 3 жыл бұрын

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¡¡¡

@josepromero1452 3 жыл бұрын

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.

@arturogonzalezgoni711 3 жыл бұрын

@@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"

@arturogonzalezgoni711 3 жыл бұрын

nada¡¡¡ ya he visto el fallo. me faltaba un ( y por eso daba un valor que no reconocía. lo voy a comprobar

@Nabb01 5 жыл бұрын

sir when table for NORMAL DISTRIBUTION IS CREATED,IT SHOWS SYNTAX ERRORS WHEN I TRIED TO WRITE "VAR"

@josepromero1452 5 жыл бұрын

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!!!

@Nabb01 5 жыл бұрын

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

@josepromero1452 5 жыл бұрын

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.

@ekadwa 5 жыл бұрын

@@josepromero1452Doing this solves the issue. Thanks for the tutorial.

@prsmem 3 жыл бұрын

Hello, where can we find the word document you showed detailing the bell curve?

@josepromero1452 3 жыл бұрын

Hello Paul. Here it is: es.slideshare.net/EdgardoMolinaOchoa/distribucion-normal-modelos-de-probabilidad-continuos Thanks for watching!!

@josejulianvalles2381 4 жыл бұрын

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,

@josepromero1452 4 жыл бұрын

Hola José Julián, desgraciadamente hay pocas publicaciones decentes sobre este tema en español... Un saludo, Josep.

@RenatoQuinzio 4 жыл бұрын

Great!

@josepromero1452 4 жыл бұрын

Thanks a lot Renato!!

@joshschaffer3142 4 жыл бұрын

Nice!

@josepromero1452 4 жыл бұрын

Thanks a lot! Josep

@sidneycavalcant 4 жыл бұрын

O excel baixado, não está igual ao exibido no vídeo, pode atualizar ?

@josepromero1452 4 жыл бұрын

Hi Sidney, Follow Parts II and III videos. Regards!!

@felipebrondani2270 4 жыл бұрын

I can t find the file with the colestoral data, somebody can help me?

@josepromero1452 4 жыл бұрын

Hello Felipe, you can find both files in the link below description.

@thetpaingmyo9077 3 жыл бұрын

@@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.

@josepromero1452 3 жыл бұрын

@@thetpaingmyo9077 Hello Thet Paing Myo, now it is ready to download. Thanks for the info!