Calculating Averages in a Pivot table

[jblank]

Hello,

Following simple question: 

In a pivot table with multiple dimensions, I want to show Actuals, as well as 12-Month Averages. So my question is for a chart function.

While I got it working for the main Category using following formula (consistently 1000 in below sample), I have difficulties to get the same by Sub Category.

This is the formula that does the overall Average:
Sum(TOTAL{$<[Link_Table.Theme] = {'link_table'}, [Events.event_name] *={event1},[MasterCalendar.MonthYear]={">=$(=Date(AddMonths(today(),-12),'MMM-YYYY')) <=$(=Date(AddMonths(today(),-1),'MMM-YYYY'))"} > }[Events.event_count])/12.

Can you help in getting the 12 month average even by Sub Category (and potentially for even more Categories beyond)?

Thank you!

	Sub Category	Month Amount	12-MTH AVG Total	12-MTH AVG by Sub Category
A	A1	12000	1000	?
A	A2	9000	1000	?
B	B1	13000	1000	?

[Clement15 · Accepted solution]

Hello,

If you use Total<[Subcategory]> instead of Total, it will ignore the Subcategory dimension in the total function and give you the total per Subcategory .

Is that what you want?

[jblank]

Hello Clement15,
Thank you - that´s what I was looking for - tried all kinds of AGGR but didn´t succeed, so much easier.
However, do you happen to know if I could add even more dimensions such as Total<[Subcategory], [Subcategory2], [Subcategory3]> ? It does not seem to work as supposed to ...
Best Regards
jblank

[Clement15]

Hello,

For me, the syntax works well. The chosen dimensions must be in the table

[jblank]

Absolutely! Thanks again!