cancel
Showing results for
Did you mean:
Post Patron

## Average of count measure, by month and category include sum in total

Hi all, I need to calculate a monthly average of counting, by category and need to sum the averages as Total. I used a dax script below and it works great except a total. How can I fix it to get a sum of averages in a total row? Thanks in advance, Rita AVG_Count_Per_month =

AVERAGEX (

SUMMARIZE ( Table1, Table1[month-year], Table1[category], "Count", COUNT ( Table1[id] ) ), [Count] )

3 REPLIES 3
Community Support

Hi  @Ritaf ,

According to your description, I created some data to show:

Here are the steps you can follow：

1. Create calculcated column.

``Month = MONTH('Table'[Date])``
``Column = CALCULATE(AVERAGE('Table'[amount]),ALLEXCEPT('Table','Table'[Month]))``

2. Create Measure.

``````Measure 2 =
var tt=SUMMARIZE('Table','Table'[Month],"avg_amount",CALCULATE(AVERAGE('Table'[amount])))
return
IF(HASONEVALUE('Table'[Month]),MAX('Table'[Column]),SUMX(tt,[avg_amount]))``````

3. Result.

Best Regards,

Liu Yang

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

Super User IV

@Ritaf , Try if this can work

AVG_Count_Per_month =
var _tab = SUMMARIZE ( Table1, Table1[month-year], Table1[category], "Count", COUNT ( Table1[category] ) )
return
sumx(summarize(_tab,[month-year], "_1", average([Count])),[_1])

Proud to be a Super User!

Post Patron

unfortunately, it isn't working...

Announcements