Forum Discussion

michaelmichael's avatar
michaelmichael
Icon for Helper I rankHelper I
6 years ago
Solved

Average and StDev lines on different aggregate charts

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 fo...
Need Help
Tutorial Requests
  • lc_finance's avatar
    lc_finance
    6 years ago

    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's avatar
michaelmichael
Icon for Helper I rankHelper I
6 years ago

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's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
6 years ago

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!

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
    michaelmichael
    Icon for Helper I rankHelper I
    6 years ago

    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's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
    lc_finance
    Icon for Solution Sage rankSolution Sage
    6 years ago

    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