topic Re: Bell Curve using Dax in DAX Commands and Tips

Bell Curve using Dax

Gezz — Thu, 04 Aug 2022 10:16:24 GMT

Hi,

I have a table TTF Raw Data + Formulas Added - with a column called Average Days - I have created a measure for Mean, Median, Standard Deviation and X-3σ and X+3σ - figures below.

Mean = 52.95652174

Median = 50

Standard Deviation = 19.623

X-3σ = -5.92

X+3σ = 111.83

I would like to create a bell curve.

My next step was to create a measure for Normal Distribution e.g. NORM.DIST(X, Mean, Standard_dev, cumulative. But this is where I get stuck - any help is appreciated.

Re: Bell Curve using Dax

lbendlin — Sat, 06 Aug 2022 01:17:08 GMT

You have your six sigma boundaries. First step is to map these out it in a new table ( or data from an existing table. Curious how you can have negative TTF though )

Table = GENERATESERIES(-10,120)

Next you add a calculated column that computes the normal distribution

ND = NORM.DIST([Value],52.956,19.623,FALSE())

Last step is to plot it in a line chart or column chart.

Re: Bell Curve using Dax

daXtreme — Sun, 07 Aug 2022 11:09:31 GMT

Hi Gezz

You don't want a dynamic measure(s) and a dynamic chart? This needs to be static? I thought you wanted to be able to plot such a curve for a dynamic selection of a subset of your data...

Re: Bell Curve using Dax

Gezz — Sun, 07 Aug 2022 11:27:54 GMT

TTF is average time to fill -5.92 is -3 standard deviations from the mean. It's not an actual data point. 52.95 is mean - 19.62 is standard deviation. 52.92 -3*19.62.

I have a column that I have calculated which is =networkdays in the Excel spreadsheet. That gives me my average days for each job. I've then calculated mean, median, standard deviation from that. If there's a way to auto calculate that from dates alone that would help when uploading a new report

Re: Bell Curve using Dax

Gezz — Sun, 07 Aug 2022 11:29:46 GMT

Ideally I'd like to set it up so that when I load a whole new excel sheet it will auto update - but working on how to do a bell curve is first step

Re: Bell Curve using Dax

Gezz — Tue, 09 Aug 2022 09:34:14 GMT

Hi Inbendlin

Thanks for your help.

I created a table using GENERATESERIES and a Calculated Column.

I have a Table with one column from with numbers -5 through to 120 and the another with the distribution values for these figures. The -5 through to 120 are the range in which all of the average days values fall within. I need the distribution values for the unique figures in my Average Days column.

In below example you have the unique student scores and the distribution for each one - I need to do that with Average Days through DAX.

I was trying to use DAX to add TFF + Forumlas Added [Average Days] as another column in the table but wasn't able to.

Any suggestions?

Re: Bell Curve using Dax

Gezz — Tue, 09 Aug 2022 09:36:23 GMT

I responded to Ibendlin below - are you able to help with distributions for each unique data point?

Re: Bell Curve using Dax

lbendlin — Tue, 09 Aug 2022 12:24:23 GMT

It is not clear to me what your expected outcome is. I can not help you without usable sample data. Screenshot are not usable.

Re: Bell Curve using Dax

Gezz — Tue, 09 Aug 2022 12:43:27 GMT

Hi Ibendlin,

I have a column called Averages Days with a series of unique values, e.g 11, 15, 42, 47, 56, 112 etc. I want to plot those with their distribution - so for each value it would be - norm.dist (11, 52.95, 19.62, false) . I would then get a value 0.0011 etc. I could then use the distribution values and the average days value in a histogram to get a skewed bell curve.

I need one column with all the distribution values and one with all the values from Average Days.

I made a table and need a column that refers to the values listed in the Average Days column which is a separate spreadsheet and then I can do a norm.dist on it I think.