Forum Discussion
Helper IAverage and StDev lines on different aggregate charts
Hi michaelmichael ,
Could you share a sample Power BI file? That will make it easier to help you.
You can upload your Power BI file to One Drive, Google Drive or other similar tool and share it here.
Let me know,
LC
Interest in Power BI and DAX templates? Check out my blog at www.finance-bi.com
Helper I
This chart looks like something I'm after yes, but I don't understand where you got the [Average Line] from in the CALCULATE function??
Community Champion
Regarding the "average line", I was just using that to explain what I was showing in my example (ie. if you were interested in displaying the St dev vs your overall average; now I see you wish to display the st dev compared to each individual groups st dev. - so my confusion, apologies).
As regards the total of 3,29, it is probably the st dev calculated on the total values, and not the actual average of the st dev individual values. If you want to calculate the average of the each value in your Excel column "StDev of Values", you will need to change the calculation to something along the lines of:
Average St Dev (average of individual stdev) =
VAR calc = CALCULATE(AVERAGEX(Table [x-values], [StDev of Values]))
Return
CALCULATE(calc, ALLSELECTED(Table [x-values]))
Try this and see if it works for you (though it is difficult to work accurately if we don't have access to the real data/data model!).
- PaulDBrown
Here you go:
To calculate the average of the Sdt dev values, I used:
Std Dev AverageX = AVERAGEX(ALLEXCEPT('Table'; 'Table'[Values]); [Standard Deviation])Please check that the other calculations/Lines are what you are after! (though I have a sneaking suspicion that negative values are not the appropriate reference you need, correct?)
- michaelmichael
PaulDBrown I'm using lc_finance supplied example, uploaded in a previous reply.
Using that dashboard, I tried getting a formula/measure that would be accept by PowerBI:
Average St Dev (average of individual stdev) = VAR calc = CALCULATE(AVERAGEX('Table',[values])) Return CALCULATE(calc, ALLSELECTED('Table'[x-axis]))But this returned some strange numbers.I appreciate all the help, sorry I'm pretty new to this software. - PaulDBrown
michaelmichael
I guess that's what happens when you (in other words me!) are guessing as to how the data is structured (columns, measures...).I suggest you try creating the measures with your own data and see if you still encounter problems/errors.
I think you have the guidelines to create the constant averages using the ALL or ALLSELECTED.
If you need further help, please provide a sample of your data (or recreate a small subset with fake values). Otherwise I feel I'm taking a bit of a shot in the dark!
- michaelmichael
PaulDBrown yes I'll keep trying
I'm working from this data set https://finance-bi.com/wp-content/uploads/2019/10/Standard-deviation.zip
It's in the same format as my data, with the same aggregates on the visual
- michaelmichael
PaulDBrown fantastic! Thanks for that.
I've got these tables in the file to work for what I want to do
I've made two tables for Standard Deviation and Average and used STDEVX.P to calculate the upper and lower lines, as shown below
But if you add a slicer onto the visuals, and start to filter the data, the averages don't update. Is it possible to have them dynamic?
- PaulDBrown
Great! glad we are getting there! I hadn't checked with slicers, sorry about that.
Better use the following measures:
St Dev av SUMMARIZE = AVERAGEX(SUMMARIZE('Table'; 'Table'[x-axis]); [Standard Deviation])St Dev SUMMARIZE (all) = CALCULATE([St Dev av SUMMARIZE]; ALLSELECTED('Table'))Which will give you this:
and you will need to adjust the other measures accordingly (substituting the old AverageX with the new measure).
See if that solves it.
PS. I've saved the it to the same PBI file, so you should be able to access it from the previous link
lc_financeSolution Sage
Hi michaelmichael ,
looks like PaulDBrown has the solution for you. If that does not work for you, do not hesitate to let us know,
LC