Forum Discussion

Kauto2007's avatar
Kauto2007
Regular Visitor
2 years ago
Solved

Calculate Average by Month and Category

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:

 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

4 Replies

  • xifeng_L's avatar
    xifeng_L
    Icon for Super User rankSuper User
    2 years ago

    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~

     

     

     

  • Kauto2007's avatar
    Kauto2007
    Regular Visitor
    2 years ago

    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.
  • Kauto2007's avatar
    Kauto2007
    Regular Visitor
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.