showing max in text box

[#1]

I have a set anaylsis in straight table which is sorted bu max price/cost with product dimension I would like to show max price/cost product in text box.

[Miguel_Angel_Baeyens]

Hi,

The following expression should do

=Num(
Max({1} TOTAL Aggr(Sum({1< Employee = {'Jae Pak', 'Tsvi Reiter'} >} UnitPrice)
/
Sum({1< Employee = {'Jae Pak', 'Tsvi Reiter'} >} TotalProductCost), Product))
, '#,##0%') 

The "{1} TOTAL" part is needed because of the locked value in YearMonth field. Using instead

=Num(
Max({< [$Year Month] = >} Aggr(Sum({1< Employee = {'Jae Pak', 'Tsvi Reiter'} >} UnitPrice)
/
Sum({1< Employee = {'Jae Pak', 'Tsvi Reiter'} >} TotalProductCost), Product))
, '#,##0%') 

should work as well

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

how can I show product name on text box

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

This should do

=Only({1} If(
Aggr(
Rank( 
Sum({1< Employee = {'Jae Pak', 'Tsvi Reiter'} >} UnitPrice) / Sum({1< Employee = {'Jae Pak', 'Tsvi Reiter'} >} TotalProductCost), 4, 1)
, Product) = 1
, Product)) 

Kind of cumbersome because of the selection, that you want to ignore, hence all those {1}.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#5]

you are perfect Miguel thank you

last question::)))  is there any special meaning of 4

[Miguel_Angel_Baeyens]

Hi,

I'm glad to help. that ", 4, 1" refers to the ranking function, that allows several parameters. Actually that will work if you remove that part, but I'm used to specify it, even when there is no need.

Rank(Something, 4, 1)

Where "Something" is an expression reads "return the rank of something, giving each row a value, and in case two rows share the same rank, show the lowest value, (i. e.: instead of showing 2-3, 2-3, the above should return 2, 3).

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica