topic Re: Overlaying a bell curve on a histogram using DAX in DAX Commands and Tips

Overlaying a bell curve on a histogram using DAX

Anonymous — Sun, 09 Aug 2020 15:21:42 GMT

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!

Re: Overlaying a bell curve on a histogram using DAX

lbendlin — Sun, 09 Aug 2020 18:07:21 GMT

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?

Re: Overlaying a bell curve on a histogram using DAX

Anonymous — Sun, 09 Aug 2020 19:08:15 GMT

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.

Re: Overlaying a bell curve on a histogram using DAX

mahoneypat — Sun, 09 Aug 2020 19:22:42 GMT

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

Re: Overlaying a bell curve on a histogram using DAX

Greg_Deckler — Sun, 09 Aug 2020 19:26:14 GMT

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.

Re: Overlaying a bell curve on a histogram using DAX

Anonymous — Mon, 10 Aug 2020 04:52:27 GMT

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

Re: Overlaying a bell curve on a histogram using DAX

Anonymous — Mon, 10 Aug 2020 17:32:46 GMT

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.

Re: Overlaying a bell curve on a histogram using DAX

mahoneypat — Mon, 10 Aug 2020 23:12:19 GMT

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

Re: Overlaying a bell curve on a histogram using DAX

Anonymous — Tue, 11 Aug 2020 06:32:26 GMT

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

Re: Overlaying a bell curve on a histogram using DAX

Anonymous — Sat, 05 Mar 2022 12:07:38 GMT

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.

Re: Overlaying a bell curve on a histogram using DAX

mahoneypat — Sat, 05 Mar 2022 12:55:28 GMT

Anonymous 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

Re: Overlaying a bell curve on a histogram using DAX

Anonymous — Sat, 05 Mar 2022 17:49:49 GMT

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?

Re: Overlaying a bell curve on a histogram using DAX

Anonymous — Sat, 05 Mar 2022 18:01:35 GMT

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

Re: Overlaying a bell curve on a histogram using DAX

mahoneypat — Sat, 05 Mar 2022 22:39:49 GMT

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

Re: Overlaying a bell curve on a histogram using DAX

Anonymous — Sat, 05 Mar 2022 22:45:37 GMT

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?