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
Community Champion
When you say "But when I try your suggested formula for a new measure or column it won't let me use [Standard Deviation of Sub Group Values] as an expression after CALCULATE?", are you trying to create a new measure? If [Standard Deviation of Sub Group Values] is a measure, you should be able to include the measure within a CALCULATE function. If it is a column, you need to use an aggregator in the CALCULATE function. (for example, CALCULATE(AVERAGE(table [Standard Deviation of Sub Group Values]), ….)
Using measures: (I have called the table which includes your "X axis" data 'Table_Values')
a) to display the total St Dev in all rows, the 0.13 total value in your example, which you can then use as a continuous line in a graph to show the total standard deviation) your measure should look like:
Standard Deviation (all) = CALCULATE ([Standard Deviation of Sub Group Values], ALLSELECTED('Table_Values'))
b) to display the "0.13 + StDev of the values", create another measure:
'0.13' + STDev = [Standard Deviation (all] + [Standard Deviation of Sub Group Values]
c) If on the other hand you want to display the absolute St Dev dispersion values from the mean, you will need (based on the measure you posted in your original message)
3 Measures:
Average Line (all) = CALCULATE([Average Line], ALLELECTED ('Table_Values'))
Average + 1 St Dev = [Average Line (all)] + [Standard Deviation (all)]
Average - 1St Dev = [Average Line (all)] - [Standard Deviation (all)]
(In all these examples I am using ALLSELECTED which is more flexible if you are going to use slicers)
Let us know if we have helped you solve the problem.
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??
- 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?)
- PaulDBrown
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!).
- 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