Average and StDev lines on different aggregate charts

[michaelmichael]

I'm trying to develop some control charts on PowerBI... To do this I need to have a line on each chart that can have a Mean, Mean+StDev and Mean-StDev

To do this I'm using this measure formula for the average lines:

Average Line = CALCULATE(AVERAGEX('Table',[Column]),ALLSELECTED('Table'))

However, I am using 3 types of control charts... Sum, Average and Standard Deviation of each subgroup (x values), therefore, this formula does not produce average lines correctly on all the charts.

The Sum chart is low, as it's using average, the Avergae chart is not in line with the Power BI calculated average and the StDev chart is not possible to produce a line for using this formula

 

What I would like help with is how to produce the lines for all 3 charts, and how to add StDev lines too

Thanks

[lc_finance · Accepted solution]

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

[michaelmichael]

@lc_finance It is a very simple data table.

I have 2 columns, 1st is the supgroup (x axis), let's call these A-J. The 2nd column is a numerical value.

You can build a simple data set of A-J, with their corresponding data value (for this excercise this can be 10 entries for each letter, number between 1-10).

From this I produce 3 visuals, and with the data I have each visual as "Average", "StDev" and "Sum".

Thanks

[michaelmichael]

@lc_finance if it helps, I've found a PowerBI Add On which can give these visual results, called Craydec Control Charts

But this costs to run, when I'm certain I can script the file to run the same thing

[lc_finance]

Hi @michaelmichael ,

I am glad you found a solution!

Do not hesitate if you have further questions

LC

[michaelmichael]

@lc_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

[PaulDBrown]

@michaelmichael 

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

[PaulDBrown]

@michaelmichael 

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]

@PaulDBrown 

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]

@michaelmichael 

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

[PaulDBrown]

@michaelmichael 

Here you go:

Standard Deviation PBI File 

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 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]

@michaelmichael 

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_finance]

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

[lc_finance]

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: