5 Replies Latest reply: Apr 14, 2016 12:54 PM by Christof Gruber

# Pivot - background color based on other rows result

Hi all,

I would like to change the background color of some cells inside a pivot, based on the measure result in another row. So lets say I have the following pivot table:

customerarticleJanFeb
cust Aart 15002000
art 21000500
cust Bart 110001000
art 2600700

... I would have the following result:

customerarticleJanFeb
cust Aart 15002000
art 21000500
cust Bart 110001000
art 2600700

So the bg color expression should be something like

If(article = 'art 2', If(Sum([Sales]) < Sum({<article='art 1'>} Total [Sales]), red()))

... which does not work :-(

Anyone has an idea?

Thanks, Christof

• ###### Re: Pivot - background color based on other rows result

You missed the curly brackets, may be this:

If(article = 'art 2', If(Sum([Sales]) < Sum({<article= {'art 1'}>} Total [Sales]), red()))

• ###### Re: Pivot - background color based on other rows result

I think, based on the colors I am seeing, you might need this:

If(article = 'art 2', If(Sum([Sales]) < Sum({<article= {'art 1'}>} [Sales]), red()))

• ###### Re: Pivot - background color based on other rows result

You're right, the missing curly brackets were just a typo in my post ...

Sadly this formula, also without the "Total", does not work ... It seems like it simply ingores the set analysis if I drop the "Total". Based on some test I did, I guess that the result for Sum({<article= {'art 1'}>} [Sales]) is always 0.

If I include the "Total" it ignores the columns and rows value :-(

Do you have any other idea?

• ###### Re: Pivot - background color based on other rows result

Is this a pivot table with three dimensions and 1 expression or straight table with 2 dimensions and 2 expressions. If its a straight table, try this:

If(article = 'art 2', If(Sum([Sales]) < Sum({<article= {'art 1'}>} TOTAL <customer> [Sales]), red()))

• ###### Re: Pivot - background color based on other rows result

yesss.. you're the man! ... that did the trick

the pivot table does have a lot of rows and only one column (month). so i did just include all the rows and the column inside <> after total (but without article), which then worked for me.

If(article = 'art 2', If(Sum([Sales]) < Sum({<article= {'art 1'}>} TOTAL <month,customer,and,all,other,rows,except,article> [Sales]), red())) ... did the job for me.

Perfect, thank you very much!