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 Ilc_finance Sorry for the confusion, I have not found a solution. I have found a paid Add On, but I'd like to do this myself using Measures.
I pressed solution by accident
Community Champion
It is very helpful to see the data in a table format when things don't seem to work out.
Here is an example:
So, to get the average Line and Standard Deviation Values you need to have a measure which removes the filter context, ie an ALL or ALLSELECTED.
The column in green for example is a simple CALCULATE([Aver. - std dev calc], ALLSELECTED(Values_Table)).
You will need the equivalent measure for your average for it to plot on the Line Chart Visual.
Hope this helps.
Best.
- michaelmichael
PaulDBrown thanks for your help, putting the data into table view, rather than line graph helps.
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?
Sorry my formula knowledge is lacking somewhat, but what I'm after is a whole column in this table visual displaying 0.13, and then one with 0.13+StDev of the values
Thanks
lc_financeSolution Sage
Hi michaelmichael ,
You can find attached an example for the whole column showing the total standard deviation.
Total Standard deviation = CALCULATE(STDEV.P('Table'[Values]), ALL('Table'[x-axis]))As PaulDBrown mentioned, you can use ALL or ALLSELECTED for that.
This is what it looks like:
Does this help you?
Regards,
LC
Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com
- michaelmichael
lc_finance PaulDBrown Both, thanks for your help. This does work now, I've got the line across the graph, however the "Total" is not giving me a mean of the values. The mean/average should be 3.08, not 3.29?
From the example above, I've drawn up in Excel what I'm trying to get my table to look like:
- PaulDBrown
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.
- michaelmichael
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??