I explain the case with which I have been fighting for a couple of days.
I need to get the average value of a group of maximum values. The example would look something like:
In a table I am giving the maximum sales value per customer, in this case, the maximum values, per year and customer would be 100 - 80 for 2014, 150 - 100 for 2015 and 80 - 70 for 2016.
On the other hand, in another table, I need to calculate the average of the maximum sales per customer, this time without grouping per year. Thus, for client A (100 + 150 + 80) / 3 = 110, and for client B (80 + 60 + 70) / 3 = 83.3.
I am trying to get that value by using the AGGR function but I can not find the right grouping.
My test is something like this:
= Aggr (MAX [[Sales]), [Client], [Year]
Can it be done with AGGR? Is there any way to do it?
Is this something you are looking for ?
Dim -> YEAR,
Expr -> = Max(SALES)
Dim -> CUSTOMER
Expr-> = Num(Sum(Aggr(MAX(SALES), CUSTOMER,YEAR ))/Count(Aggr(MAX(SALES), YEAR,CUSTOMER )), '#,##0.00')
When you said it's not working? Can you explain little more here? Which dimensions you have used? What is the expression you have used?
The expression I gave was for 2nd requirement.
For both of your requirements, do you want to use CUSTOMER and YEAR both as Dimensions?