Pivot - background color based on other rows result

[#1]

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:

customer	article	Jan	Feb
cust A	art 1	500	2000
	art 2	1000	500
cust B	art 1	1000	1000
	art 2	600	700

... I would have the following result:

customer	article	Jan	Feb
cust A	art 1	500	2000
	art 2	1000	500
cust B	art 1	1000	1000
	art 2	600	700

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

[sunny_talwar]

You missed the curly brackets, may be this:

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

[sunny_talwar]

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()))

[#4]

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?

[sunny_talwar · Accepted solution]

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()))

[#6]

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!