community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Create Table for Bell Curve

Highlighted

I am trying to use alteryx to create a table for a bell curve visual in PowerBI because PowerBI does not have a dynamic table capability (yet). 

 

My initial data is the following (just a sample)

 

Request IDTotal Time TakenFYCreated Date
1518/1907/18/2019
21018/1907/01/2019
32017/1808/01/2017
43017/1801/01/2018

 

Using the summarize tool, I have performed the following actions on the data:

  • Group by FY
  • Min of Created Date
  • Max of Created Date
  • AvgNo0 of Total Time Taken
  • StdDevNo0 of Total Time Taken
  • Min  of Total Time Taken
  • Max  of Total Time Taken

I then use a formula tool to create the x-3a and x+3a values (i.e. the min and max's of the array for the bell curve) 'x' being the mean and 'a' being the standard deviation.

I then use a generate rows tool to generate the array ranges for each fiscal year. 

 

This results in the following which is what I want. 

FYMin_Created Date-Date OnlyMax_Created Date-Date OnlyMeanStandard DevMin_Total Time (Business Days)Max_Total Time (Business Days)X-3AX+3aValue
16/1710/3/20165/31/201717120.01113.1-1853-18
16/1710/3/20165/31/201717120.01113.1-1853-17
16/1710/3/20165/31/201717120.01113.1-1853-16
16/1710/3/20165/31/201717120.01113.1-1853-15
16/1710/3/20165/31/201717120.01113.1-1853-14
16/1710/3/20165/31/201717120.01113.1-1853-13
16/1710/3/20165/31/201717120.01113.1-1853-12
16/1710/3/20165/31/201717120.01113.1-1853-11
16/1710/3/20165/31/201717120.01113.1-1853-10
16/1710/3/20165/31/201717120.01113.1-1853-9
16/1710/3/20165/31/201717120.01113.1-1853-8
16/1710/3/20165/31/201717120.01113.1-1853-7
16/1710/3/20165/31/201717120.01113.1-1853-6
16/1710/3/20165/31/201717120.01113.1-1853-5
16/1710/3/20165/31/201717120.01113.1-1853-4
16/1710/3/20165/31/201717120.01113.1-1853-3
16/1710/3/20165/31/201717120.01113.1-1853-2
16/1710/3/20165/31/201717120.01113.1-1853-1
16/1710/3/20165/31/201717120.01113.1-18530
16/1710/3/20165/31/201717120.01113.1-18531
16/1710/3/20165/31/201717120.01113.1-18532
16/1710/3/20165/31/201717120.01113.1-18533
16/1710/3/20165/31/201717120.01113.1-18534
16/1710/3/20165/31/201717120.01113.1-18535
16/1710/3/20165/31/201717120.01113.1-18536
16/1710/3/20165/31/201717120.01113.1-18537
16/1710/3/20165/31/201717120.01113.1-18538
16/1710/3/20165/31/201717120.01113.1-18539
16/1710/3/20165/31/201717120.01113.1-185310
16/1710/3/20165/31/201717120.01113.1-185311
16/1710/3/20165/31/201717120.01113.1-185312
16/1710/3/20165/31/201717120.01113.1-185313
16/1710/3/20165/31/201717120.01113.1-185314
16/1710/3/20165/31/201717120.01113.1-185315
16/1710/3/20165/31/201717120.01113.1-185316
16/1710/3/20165/31/201717120.01113.1-185317
16/1710/3/20165/31/201717120.01113.1-185318
16/1710/3/20165/31/201717120.01113.1-185319
16/1710/3/20165/31/201717120.01113.1-185320
16/1710/3/20165/31/201717120.01113.1-185321
16/1710/3/20165/31/201717120.01113.1-185322
16/1710/3/20165/31/201717120.01113.1-185323
16/1710/3/20165/31/201717120.01113.1-185324
16/1710/3/20165/31/201717120.01113.1-185325
16/1710/3/20165/31/201717120.01113.1-185326
16/1710/3/20165/31/201717120.01113.1-185327
16/1710/3/20165/31/201717120.01113.1-185328
16/1710/3/20165/31/201717120.01113.1-185329
16/1710/3/20165/31/201717120.01113.1-185330
16/1710/3/20165/31/201717120.01113.1-185331
16/1710/3/20165/31/201717120.01113.1-185332
16/1710/3/20165/31/201717120.01113.1-185333
16/1710/3/20165/31/201717120.01113.1-185334
16/1710/3/20165/31/201717120.01113.1-185335
16/1710/3/20165/31/201717120.01113.1-185336
16/1710/3/20165/31/201717120.01113.1-185337
16/1710/3/20165/31/201717120.01113.1-185338
16/1710/3/20165/31/201717120.01113.1-185339
16/1710/3/20165/31/201717120.01113.1-185340
16/1710/3/20165/31/201717120.01113.1-185341
16/1710/3/20165/31/201717120.01113.1-185342
16/1710/3/20165/31/201717120.01113.1-185343
16/1710/3/20165/31/201717120.01113.1-185344
16/1710/3/20165/31/201717120.01113.1-185345
16/1710/3/20165/31/201717120.01113.1-185346
16/1710/3/20165/31/201717120.01113.1-185347
16/1710/3/20165/31/201717120.01113.1-185348
16/1710/3/20165/31/201717120.01113.1-185349
16/1710/3/20165/31/201717120.01113.1-185350
16/1710/3/20165/31/201717120.01113.1-185351
16/1710/3/20165/31/201717120.01113.1-185352
16/1710/3/20165/31/201717120.01113.1-185353
17/186/1/20175/31/20181070.1964.47-1031-10
17/186/1/20175/31/20181070.1964.47-1031-9
17/186/1/20175/31/20181070.1964.47-1031-8
17/186/1/20175/31/20181070.1964.47-1031-7
17/186/1/20175/31/20181070.1964.47-1031-6
17/186/1/20175/31/20181070.1964.47-1031-5
17/186/1/20175/31/20181070.1964.47-1031-4
17/186/1/20175/31/20181070.1964.47-1031-3
17/186/1/20175/31/20181070.1964.47-1031-2
17/186/1/20175/31/20181070.1964.47-1031-1
17/186/1/20175/31/20181070.1964.47-10310
17/186/1/20175/31/20181070.1964.47-10311
17/186/1/20175/31/20181070.1964.47-10312
17/186/1/20175/31/20181070.1964.47-10313
17/186/1/20175/31/20181070.1964.47-10314
17/186/1/20175/31/20181070.1964.47-10315
17/186/1/20175/31/20181070.1964.47-10316
17/186/1/20175/31/20181070.1964.47-10317
17/186/1/20175/31/20181070.1964.47-10318
17/186/1/20175/31/20181070.1964.47-10319
17/186/1/20175/31/20181070.1964.47-103110
17/186/1/20175/31/20181070.1964.47-103111
17/186/1/20175/31/20181070.1964.47-103112
17/186/1/20175/31/20181070.1964.47-103113
17/186/1/20175/31/20181070.1964.47-103114
17/186/1/20175/31/20181070.1964.47-103115
17/186/1/20175/31/20181070.1964.47-103116
17/186/1/20175/31/20181070.1964.47-103117
17/186/1/20175/31/20181070.1964.47-103118
17/186/1/20175/31/20181070.1964.47-103119
17/186/1/20175/31/20181070.1964.47-103120
17/186/1/20175/31/20181070.1964.47-103121
17/186/1/20175/31/20181070.1964.47-103122
17/186/1/20175/31/20181070.1964.47-103123
17/186/1/20175/31/20181070.1964.47-103124
17/186/1/20175/31/20181070.1964.47-103125
17/186/1/20175/31/20181070.1964.47-103126
17/186/1/20175/31/20181070.1964.47-103127
17/186/1/20175/31/20181070.1964.47-103128
17/186/1/20175/31/20181070.1964.47-103129
17/186/1/20175/31/20181070.1964.47-103130
17/186/1/20175/31/20181070.1964.47-103131
18/196/1/20185/31/2019450.0149.59-1018-10
18/196/1/20185/31/2019450.0149.59-1018-9
18/196/1/20185/31/2019450.0149.59-1018-8
18/196/1/20185/31/2019450.0149.59-1018-7
18/196/1/20185/31/2019450.0149.59-1018-6
18/196/1/20185/31/2019450.0149.59-1018-5
18/196/1/20185/31/2019450.0149.59-1018-4
18/196/1/20185/31/2019450.0149.59-1018-3
18/196/1/20185/31/2019450.0149.59-1018-2
18/196/1/20185/31/2019450.0149.59-1018-1
18/196/1/20185/31/2019450.0149.59-10180
18/196/1/20185/31/2019450.0149.59-10181
18/196/1/20185/31/2019450.0149.59-10182
18/196/1/20185/31/2019450.0149.59-10183
18/196/1/20185/31/2019450.0149.59-10184
18/196/1/20185/31/2019450.0149.59-10185
18/196/1/20185/31/2019450.0149.59-10186
18/196/1/20185/31/2019450.0149.59-10187
18/196/1/20185/31/2019450.0149.59-10188
18/196/1/20185/31/2019450.0149.59-10189
18/196/1/20185/31/2019450.0149.59-101810
18/196/1/20185/31/2019450.0149.59-101811
18/196/1/20185/31/2019450.0149.59-101812
18/196/1/20185/31/2019450.0149.59-101813
18/196/1/20185/31/2019450.0149.59-101814
18/196/1/20185/31/2019450.0149.59-101815
18/196/1/20185/31/2019450.0149.59-101816
18/196/1/20185/31/2019450.0149.59-101817
18/196/1/20185/31/2019450.0149.59-101818
19/206/2/20197/10/2019850.0224.56-623-6
19/206/2/20197/10/2019850.0224.56-623-5
19/206/2/20197/10/2019850.0224.56-623-4
19/206/2/20197/10/2019850.0224.56-623-3
19/206/2/20197/10/2019850.0224.56-623-2
19/206/2/20197/10/2019850.0224.56-623-1
19/206/2/20197/10/2019850.0224.56-6230
19/206/2/20197/10/2019850.0224.56-6231
19/206/2/20197/10/2019850.0224.56-6232
19/206/2/20197/10/2019850.0224.56-6233
19/206/2/20197/10/2019850.0224.56-6234
19/206/2/20197/10/2019850.0224.56-6235
19/206/2/20197/10/2019850.0224.56-6236
19/206/2/20197/10/2019850.0224.56-6237
19/206/2/20197/10/2019850.0224.56-6238
19/206/2/20197/10/2019850.0224.56-6239
19/206/2/20197/10/2019850.0224.56-62310
19/206/2/20197/10/2019850.0224.56-62311
19/206/2/20197/10/2019850.0224.56-62312
19/206/2/20197/10/2019850.0224.56-62313
19/206/2/20197/10/2019850.0224.56-62314
19/206/2/20197/10/2019850.0224.56-62315
19/206/2/20197/10/2019850.0224.56-62316
19/206/2/20197/10/2019850.0224.56-62317
19/206/2/20197/10/2019850.0224.56-62318
19/206/2/20197/10/2019850.0224.56-62319
19/206/2/20197/10/2019850.0224.56-62320
19/206/2/20197/10/2019850.0224.56-62321
19/206/2/20197/10/2019850.0224.56-62322
19/206/2/20197/10/2019850.0224.56-62323

 

Next, I need to do the following and this is where I am stuck trying to figure out how to do this. 

  1. Calculate the Norm Distribution for each array value. In excel this is =NORM.DIST([ARRAYVALUE],[MEAN],[STND DEV],FALSE)
  2. Calculate the ranges within the bell curve array for the Total Time Taken 
  3. Count how many requests fall into each region and then calculate a percentage

Ultimately in PowerBI, I want to end up with something like the following but also include labels of the Total Time Taken and the counts and percentages but in order to do this, I need to feed PowerBI the table or tables.

Bellcurveexample.png

 

Open to any suggestions on how to do this or if potentially there is a better way to do this...

 

 

 

Nebula
Nebula

Hi @Shelton_Thompson 

 

I can get you the next step of the way.  Using this formula taken from here,  the attached  workflow calculates the y values for your bell curves using this formula

 

Formula.png

which translates to this in Alteryx

 

1/SQRT(2*pi()*pow([Standard Dev],2))*exp(-pow(([Value]-[Mean]),2)/(2*pow([Standard Dev],2)))

 

Curves.png

 

Dan

 

Thank you! That works perfectly! now just have to figure out how to plot the points to show the average time taken at each point and get the percentages to define the areas.

 

Reading your link my 'theory' is I could calculate the z-score for each record, then use a standard normal distribution table to determine the percentage and plotting values. Not sure yet how I will make that work, but time to play! If anyone has any ideas in the meantime, I am open to them!

Alright I got it! I took it a step further and also created a Macro so no one else has to try to figure this out with a built-in Standard Normalization Table. 

 

https://gallery.alteryx.com/#!app/Bell%2BCurve/5d3739e0826fd3108052ad22

 

Labels