11 Replies Latest reply: Sep 17, 2016 12:46 PM by Dermot mcgoldrick

# Hihglight min and max in all rows of Pivot

Any way to do this, example of max's highlighted below

• ###### Re: Hihglight min and max in all rows of Pivot

Lets say your expression is Sum(Sales), then you can try this for color expression:

If(Sum(Sales) = Sum(TOTAL <Dimension1> Sales), LightGreen())

• ###### Re: Hihglight min and max in all rows of Pivot

I think the second Sum should be 'Max' -

If(Sum(Sales) = Max(TOTAL <Dimension1> Sales), LightGreen())

• ###### Re: Hihglight min and max in all rows of Pivot

Yes, you are right , but we might need this

If(Sum(Sales) = Max(TOTAL <Dimension1> Aggr(Sum(Sales), Dimension1, Mth)), LightGreen())

• ###### Re: Hihglight min and max in all rows of Pivot

You are right we have to have Aggr function to select max out of all months sum(value), but I think that is sufficient, do we need total<dim> now?

• ###### Re: Hihglight min and max in all rows of Pivot

I think we still do because for a chart with Dimension1 and Mth as dimension

the each cell value using this expression

Max(Aggr(Sum(Sales), Dimension1, Mth))

will equal

Sum(Sales)

• ###### Re: Hihglight min and max in all rows of Pivot

Yeah! We cannot disregard first dim. Thanks

• ###### Re: Hihglight min and max in all rows of Pivot

I just noticed there is a third dimension and the expression might need to be changed a little more

If(Sum(Sales) = Max(TOTAL <Dimension1, Dimension2> Aggr(Sum(Sales), Dimension1, Dimension2, Mth)), LightGreen())

• ###### Re: Hihglight min and max in all rows of Pivot

Try

If(RangeMax(First(Sum(Value),1,NoOfColumns()))=Sum(Value),Green(100))

• ###### Re: Hihglight min and max in all rows of Pivot

If You want Red as Min

If(RangeMax(First(Sum(Value),1,NoOfColumns()))=Sum(Value),Green(100),
If(RangeMin(First(Sum(Value),1,NoOfColumns()-1))=Sum(Value),Red
(100)))

Regards,

Antonio

• ###### Re: Hihglight min and max in all rows of Pivot

Brilliant guys thanks for this, will get a look at it next week, travelling with work for a few days so might not be until later in week.  A useful chart I think though for looking at say order intake and sales across categories!

• ###### Re: Hihglight min and max in all rows of Pivot

Hi,

Try this expression

IF(SUM(Value)=RANGEMAX(TOP(TOTAL SUM(Value),1,NOOFROWS(TOTAL))),GREEN()

,IF(SUM(Value) = RANGEMIN(TOP(TOTAL SUM(Value),1,NOOFROWS(TOTAL))),RED()))

Hope it helps