cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gezz
Regular Visitor

Bell Curve using Dax

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.

 

8 REPLIES 8
daXtreme
Super User
Super User

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

Gezz
Regular Visitor

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

Gezz
Regular Visitor

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 

lbendlin
Super User
Super User

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.

lbendlin_0-1659748382888.png

lbendlin_1-1659748442635.png

 

 

Gezz
Regular Visitor

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.

 

Capture.PNG

 

 

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?

 

 

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

Gezz
Regular Visitor

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.

Gezz
Regular Visitor

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 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors