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

Power BI Question 2 

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

 

Thanks

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

19 Replies

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

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

        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