MIN within a group, Average

[#1]

My data looks like the following

Prod_Grp----prod_id------ date----------price

A---------------101 -----------15/1/2010---$9,99
A---------------101 -----------21/6/2010---$8,99
A---------------101 -----------22/12/2010--$10,99
A---------------102------------6/1/2010-----$7,49
B---------------103...ect

I want to know the average MINIMUM price on Prod_Grp level within a certain choosed period.

The minimum price on PROD_ID level was no problem for me:

MIN

({<[YEAR]={$(=maxstring([YEAR]))}>}[Price per unit])

But know I need the average MINIMUM price on Prod_Grp level so in my example, when Year is set to 2010:

A: (8,99+7,49)/2 = 8,24

[brenner_martina · Accepted solution]

Hi,

you need to aggregate the Values by one or more dimension(s), something like:

Avg(AGGR(MIN({<[YEAR]={$(=maxstring([YEAR]))}>}[Price per unit]) ,Dimension1,Dimension2))

[#3]

With the help of the above I've created the following formula

Avg(Aggr(

MIN

({<[year]={$(=maxstring([year]))},[period]={$(=maxstring([period]))}>}[Price per unit])
,[Prod_Id] ))

I now want to make this function a little bit more dynamic so it takes in account the dimensions which are choosen to be displayed in the staright/pivot table.

Sometimes this can be "Channel" but when drill throughed it can be "Customer_group" (this one is below channel).
And MINIMUM Average price needs to be calculated accordingly (when drilled up to channel, per channel, for each prod_id
and when drilled down to Cust_grp, per cust_group for each prod_id).

How can this be done? is there something in the syntax like Dim1 , Dim2 which refers to the used dimensions in the straight/pivot table??

[#4]

I'm now experimenting with adding ALL POSSIBLE/RELEVANT dimensions adding to the AGRR() function, even if they are not used in the initial start straight/pivot table.

And then if drilled/up/down by user QV uses those dimension which are relevant.

It seems to work but not sure if this is the way to work?

[#5]

No boss. i am getting the same sales value.

I want per unit and also for each part no. for eg. if i select IN880180, the maximum sales price per unit should be 102 and minimum should be Rs. 80. the average should be 91.

likewise, it should compare the price per unit of all the shipments for each part no. and find out.