Overlaying a bell curve on a histogram using DAX

[Anonymous]

Hi all! Noob here, so apologize if a similar thread already exists! 🙂 

I'm stuck with the following issue:
As you can see in the 1st pic. below I've plotted a histogram using a line & clustered chart. So far, all good.
Now if try to plot the Gaussian Distribution on the same graph, I get errors saying there needs to be a relationship between the columns.
I tried linking my "f(values)" column from the "UTS ND" table to the calculated column of "Start" from my "UTS filtering" table as it seemed the logical thing to do using a 1 to Many relationship, but the program doesn't allow because of a circular dependency.
Had anyone else experienced a similar issue before? If so, how did you manage to overcome it?
This is a job-related task that I've been struggling with for the past few days & I've ran out of ideas. :'(
Thank you for every little bit of information!
P1 - The original graph

P2 - the calculated params. for the Gaussian distribution

P3 - the desired output

[mahoneypat · Accepted solution]

I looked at your pbix.  I think the best way to do this is to add another calculated column to your Number Filtering 2 table to calculate the normal distribution value for that range using an expression like this one.

NormDist =
VAR avgval = ( 'Number filtering 2'[Start] + 'Number filtering 2'[End] ) / 2
VAR mean =
    AVERAGE ( 'D4142 0,75 inch 2020'[UTS Values] )
VAR std =
    STDEV.P ( 'D4142 0,75 inch 2020'[UTS Values] )
VAR nd =
    NORM.DIST ( avgval, mean, std, 0 )
RETURN
    nd

You can then add that to your visual to get the below.

Does that get you what you need?

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[nrose]

Hi @mahoneypat  how did you overlay the distribution directly into the histogram? Where can I inject the distribution curve and what histogram app visual did you use? Im quite new to powerbi and tempted to just use python at this point.

[mahoneypat]

@nrose I looked for the original file to share it here, but I must have deleted it. The link to the file shared here is no longer valid either. However, I added the provided calculated column, and then I probably just used the line and clustered column chart (one of the native visuals).

Pat

[nrose]

I tried using your formula and got this. Its bimodal and very small amplitudes. Is it because Im categorizing @mahoneypat ?

[mahoneypat]

Do you have the distribution data in a separate table? The expression above is a calculated column, and the avgval variable is just the average value on a given row. The next two variables calculate the average and stdev of the values in the other table (your distribution). The Norm.Dist then returns a value (from 0 to 1) on where the average on that row falls in that distribution.

Of course, you could use the values from the original column (instead of having a 2nd table) to get the gaussian from their avg and stdev.

Pat

[nrose]

Pat thanks for the quick responses.

Im unsure, do I need to generate the distribution data?

Here is the formula I used to create a calculated column:

Do I just set the avgvalue to the currentcolumn?

[nrose]

I think I might understand, did you add this as a measure or column in Power BI
What exactly is the `avgval ` in your variable. Is that just the column range?

[Anonymous]

@mahoneypat hi there. Eventually yesterday after I sent you the link I've figured out myself after I've created a measure in the main table for my normal distribution & plotted it in my graph on the secondary Y axis. 
Your approach works just fine, too! 
Thank you to all of you for the suggestions. ^_^
The moderators can close the thread now! 😄

[mahoneypat]

If you can provide a link to your pbix, I'm sure this is doable.  You can also use TREATAS() to pass the filter from one table to the other (e.g., to get the x value to go into the gaussian calculation for that category).

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

@mahoneypat here's the link to the file: https://drive.google.com/file/d/1U3TI-SkTyC8CuP2zzs-Js66qJUEV4j33/view?usp=sharing 
If you have time & can have a look at it.... 😄

[Anonymous]

I'm not sure but if you put ranges (which are text) on the x-axis, then you'll never get a beautiful smooth gaussian line overlaid on the bar chart. I think for something like that you'll need a custom visual. Might be wrong but this is my strong feeling based on experience as well... You can get a line that's made of connected straight line segments but not a smooth gaussian line.

[lbendlin]

What's the point of P3?  You could put the same value in the column and line  sinks,  but all it would do is showing the same information in a different format?

[Anonymous]

So instead of that table, I want to show on the graph the bell curve alongside with the bar chart I've already plotted. 
P2 was like an intermediate step. 

[Greg_Deckler]

@Anonymous Seems like you would want to use NORM.DIST.

Since I see you are a New Member, Please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.