Monthly and constant average over time per category

[emkane]

Hi All,

Desperately trying to figure out a solution here. My data table 'WorkOrders' looks like this:

I need to show the monthly average count from all the locations for all work orders with a line showing the average of all months. Something like this:

I also need to show per location, per priority, and per catagory over time. 

Thanks in advance! 

[Greg_Deckler]

Not 100% on this but maybe things like:

AvgAllMonth = 
  AVERAGEX(ALL('Table'),[count])

[emkane]

Thanks, but I need to sum up the count per day (and/or month) before averaging. 

[v-lid-msft]

Hi @emkane ,

We can create  following measures to meet your requirment:

Avg Per Day =
AVERAGEX (
    GROUPBY ( 'Table', 'Table'[Complete Date] ),
    CALCULATE ( SUM ( 'Table'[count] ) )
)

Avg Per Location =
AVERAGEX (
    GROUPBY ( 'Table', 'Table'[Complete Date], 'Table'[Location] ),
    CALCULATE ( SUM ( 'Table'[count] ) )
)

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to OneDrive For Business and share the link here.

Best regards,

[emkane]

Thanks @v-lid-msft ! That works and gets me this:

 

I also need the average of Avg Per Day (722.81) I have tried to filter ALLEXCEPT, but I am still doing something wrong. 

[v-lid-msft]

Hi @emkane ,

We try to use the following measure to fix it:

Avg Per Day =
AVERAGEX (
    GROUPBY (
        'Table',
        'Table'[Complete Date].[Year],
        'Table'[Complete Date].[Month]
    ),
    CALCULATE ( SUM ( 'Table'[count] ) )
)

Best regards,

[emkane]

Thank you , 

That didn't work, and upon closer inspection, the other solutions are not giving me what I am looking for either.

I have outlined below the averages I am trying to calculate; Average Per Month of All, Monthly Average of All, Monthly Average of Location, and All Time Average.

My data set is here:

Thanks again for all the help, I am very appreciative! 

[v-lid-msft · Accepted solution]

Hi @emkane ,

We can use the following measures to meet your requirement:

Monthly Average of Location = AVERAGEX(GROUPBY(WOs,WOs[Location],WOs[Year],'WOs'[Month]),CALCULATE(SUM(WOs[count])))

Average Per Month of All = SUMX(GROUPBY(WOs,WOs[Year],WOs[Month]),AVERAGEX(GROUPBY(WOs,WOs[Year],WOs[Month],WOs[Location]),CALCULATE(SUM(WOs[count]))))

All Time Average = AVERAGEX(GROUPBY(WOs,WOs[Year],'WOs'[Month]),CALCULATE(SUM(WOs[count])))

By the way, PBIX file as attached.

Best regards,

[emkane]

Thank you!