Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
@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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
I tried using your formula and got this. Its bimodal and very small amplitudes. Is it because Im categorizing @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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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?
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?
@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! 😄
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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.... 😄
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?
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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
54 | |
26 | |
23 | |
15 | |
9 |
User | Count |
---|---|
77 | |
61 | |
47 | |
17 | |
12 |