topic Re: Pivot - background color based on other rows result in App Development

Pivot - background color based on other rows result

Thu, 14 Apr 2016 16:04:05 GMT

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

Re: Pivot - background color based on other rows result

sunny_talwar — Thu, 14 Apr 2016 16:09:10 GMT

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

sunny_talwar — Thu, 14 Apr 2016 16:11:12 GMT

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

Thu, 14 Apr 2016 16:23:42 GMT

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

sunny_talwar — Thu, 14 Apr 2016 16:42:16 GMT

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

Thu, 14 Apr 2016 16:54:58 GMT

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!