Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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()))
You missed the curly brackets, may be this:
If(article = 'art 2', If(Sum([Sales]) < Sum({<article= {'art 1'}>} Total [Sales]), red()))
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()))
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?
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()))
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!