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

     

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.