Forum Discussion

martyn_shields's avatar
martyn_shields
New Member
7 years ago
Solved

Creating a XmR Control Chart in Power BI

Hi All

 

I've looked through the forums and found some good posts on this topic but nothing that quite fits the challenge I currently have. I'm looking at replicating a XmR control chart dynamically with Power BI, using DAX I have got all but one element working.

 

The issue I have is I want to calculate the mean average of monthly actuals, not for the current financial year but for the previous financial year. I then want each financial year to replicate the same process insolation in the same column (averaging the monthly values for the previous financial year).

 

I have tried using the parallelperiod function along with datesinperiod but can't seem to achieve want I need, the raw DAX I need to run for the previous financial year is ;

 
Mean CALC =
CALCULATE (
AVERAGE (Incidents[Actual]),FILTER(ALLSELECTED(Incidents),Incidents[Actual]))

 

Here is a summarised copy of the current data structure. What I would like to achieve is in the [Average] column. For the example below the calculated figure for 2017-18 is 32.75 which is =average([Acutal] for Apr-16 to Mar-17).

 

MonthActualFinancial YearAverage
Mar-16482015-16 
Apr-16302016-17 
May-16332016-17 
Jun-16402016-17 
Jul-16382016-17 
Aug-16352016-17 
Sep-16322016-17 
Oct-16332016-17 
Nov-16302016-17 
Dec-16362016-17 
Jan-17342016-17 
Feb-17222016-17 
Mar-17302016-17 
Apr-17312017-1832.75
May-17392017-1832.75
Jun-17262017-1832.75
Jul-17202017-1832.75
Aug-17262017-1832.75
Sep-17212017-1832.75
Oct-17432017-1832.75
Nov-17262017-1832.75
Dec-17342017-1832.75
Jan-18342017-1832.75
Feb-18522017-1832.75
Mar-18292017-1832.75
Apr-18342018-1931.75
May-18492018-1931.75
Jun-18302018-1931.75
Jul-18262018-1931.75
Aug-18422018-1931.75
Sep-18282018-1931.75
Oct-18332018-1931.75
Nov-18342018-1931.75
Dec-18472018-1931.75
Jan-19322018-1931.75
Feb-19342018-1931.75
Mar-1962018-1931.75

 

Is this possible? Any help would be much appreciated as all of the DAX is starting to blend into one :smileyhappy:

 

Thanks

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi martyn_shields ,

    You could create the calculated columns below to get your desired output.

    Column =
LEFT ( 'Table1'[Financial Year], 4 )
average_ =
CALCULATE (
    AVERAGE ( Table1[Actual] ),
    ALLEXCEPT ( Table1, 'Table1'[Financial Year] ),
    FILTER ( 'Table1', 'Table1'[Column] = EARLIER ( 'Table1'[Column] ) - 1 )
)

    Here is the output.

    Best  Regards,

    Cherry

     

3 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi martyn_shields ,

    You could create the calculated columns below to get your desired output.

    Column =
LEFT ( 'Table1'[Financial Year], 4 )
average_ =
CALCULATE (
    AVERAGE ( Table1[Actual] ),
    ALLEXCEPT ( Table1, 'Table1'[Financial Year] ),
    FILTER ( 'Table1', 'Table1'[Column] = EARLIER ( 'Table1'[Column] ) - 1 )
)

    Here is the output.

    Best  Regards,

    Cherry

     

    • martyn_shields's avatar
      martyn_shields
      New Member
      7 years ago

      Hi Cherry

       

      Thanks for the quick response that works perfectly when calculating the average of a column is there any way of altering the DAX to work on a measure?

       

      I have measure that is calculating the variance between the current period and the previous month, I also need to return the average of this measure using the same rules above. I tried to alter the DAX and use an AverageX instead of an Average but I received a circular dependency error.

       

      The DAX for the variance is;

      difference = abs(sum(Incidents[Actual]) - CALCULATE(sum(Incidents[Actual]),PARALLELPERIOD('Incidents'[Month],-1,MONTH)))

       

      Any ideas please?

      • CHR15F1T2's avatar
        CHR15F1T2
        Regular Visitor
        3 years ago

        Hi Martyn,  Just picked up this thread.  I'm having exactly same issue and wondered if you ever got it figured out?  I successfully created 'current month', 'previous month', 'monthly variance'.  When I then try to calculate the average of the variance for the selected period it doesn't make sense.  At this stage I have CALCULATE(AVERAGEX(,'Calendar table',[monthly variance]),ALLSELECTED('Calendar table')).  What it appears to be returning is the average for the entire dataset, NOT the filtered range.  NB - 'monthly variance' is a calculated measure which appears to be preventing me using alternative methods I've found online.