Calculate Average by Month and Category

[Kauto2007]

A relative newbie to DAX so take it easy on me if it's a simple solution. So i have several years of data of different info and i want to calculate an average of the monthly total by a certain category as each category may have several entries per month. An example of the data and what i would expect the result to be is as follows:

[v-jiewu-msft · Accepted solution]

Hi @Kauto2007 ,

First of all, many thanks to  for your very quick and effective replies.

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new column to filter the month.

Month = MONTH('Table'[Date])

3.Create the new measure to calculate average for category.

Average = 
VAR categ = SELECTEDVALUE('Table'[Category])
VAR total_val = CALCULATE(SUM('Table'[Value]), FILTER(ALLSELECTED('Table'), 'Table'[Category] = categ))
VAR month_num = CALCULATE(DISTINCTCOUNT('Table'[Month]), 'Table'[Category] = categ)
RETURN
CALCULATE(
    DIVIDE(total_val, month_num)
)

4.Select Drag the category field into the slicer.

5.Select the Category A. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kauto2007]

I know i can just add an average in from the Analystics part of the time series analysis but i need the average to calculate the STD of the averages to create my upper and lower control limits..

[Kauto2007]

@xifeng_L  Thanks for that. I tried that measure and although it doesnt come up with any errors so it is accepted, when i add it into a visual it is blank

Monthly Average =

                IF(HASONEFILTER(FACT_Finance_Data[Staffing Category]),AVERAGEX(ALL(FACT_Finance_Data[Month_Year]),CALCULATE(SUM(FACT_Finance_Data[Agency Actual]))))

This is the measure i used.

Basically im trying to create an average for an SPC chart, which is dynamic depending on the team that is selected in the slicer, if that info helps.

[xifeng_L]

Hi @Kauto2007 ,

You can try below measure.

average of the monthly total = IF(HASONEFILTER('Table'[Category]),AVERAGEX(ALL('Table'[Month]),CALCULATE(SUM('Table'[Value]))))

Demo - Calculate Average by Month and Category.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~