Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
martyn_shields
New Member

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

 

Thanks

1 ACCEPTED SOLUTION
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

Capture.PNG

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

Capture.PNG

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors