Average of max values?

[sgonzalez_adaso]

Hello everyone,

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:

YEAR	CUSTOMER	SALES
2014	A	50
2014	B	80
2014	A	100
2014	B	30
2015	A	150
2015	B	60
2015	A	30
2015	B	100
2016	A	80
2016	B	70
2016	A	50
2016	B	40

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?

[MK_QSL]

Dimension

CUSTOMER

Expression

=Avg(Aggr(Max(SALES),CUSTOMER,YEAR))

[sgonzalez_adaso]

My mistake.

My dimension is CUSTOMER, of course.

This expression is not working for me.

[vishsaggi]

Is this something you are looking for ?

FirstTable:

Dim -> YEAR,

           CUSTOMER

Expr -> = Max(SALES)

2nd Table:

Dim -> CUSTOMER

Expr-> = Num(Sum(Aggr(MAX(SALES), CUSTOMER,YEAR ))/Count(Aggr(MAX(SALES), YEAR,CUSTOMER )), '#,##0.00')

[Anonymous · Accepted solution]

avg(DISTINCT Aggr(max(VENTAS),ANO,CLIENTE))

[MK_QSL]

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?