topic Re: Calculate Average by Month and Category in DAX Commands and Tips

Calculate Average by Month and Category

Kauto2007 — Sun, 19 May 2024 11:40:16 GMT

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:

Re: Calculate Average by Month and Category

xifeng_L — Sun, 19 May 2024 12:24:10 GMT

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~

Re: Calculate Average by Month and Category

Kauto2007 — Mon, 20 May 2024 13:28:18 GMT

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.

Re: Calculate Average by Month and Category

Kauto2007 — Mon, 20 May 2024 13:30:29 GMT

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

Re: Calculate Average by Month and Category

Anonymous — Wed, 22 May 2024 05:31:45 GMT

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.