you can add in the background color ("+" symbol on the left of your expression) this formula:
if(RowNo()=1 and sum(Amount)<>Below(sum(Amount)) or RowNo()=2 and sum(Amount)<>above(sum(Amount)), Yellow())
or this, more complicated, if user can select Credit/Debit
if((RowNo()=1 and sum(Amount)<>Below(sum(Amount)) or RowNo()=2 and sum(Amount)<>above(sum(Amount))) and GetSelectedCount([Credit/Debit])<>1, Yellow())
Let me know!
LOAD * INLINE [
Accounts, Credit/Debit, Amount
Account 1, C, 400
Account 1, D, 500
Account 2, C, 1200
Account 2, D, 1200
Account 3, C, 300
Account 3, D, 350
,IF(Previous(Accounts)=Accounts AND Previous([Credit/Debit]) <> [Credit/Debit] AND Previous(Amount) <> Amount,1,0) AS Check
DROP Table Temp;
And in the chart go to Chart -> Properties -> Expression -> Background -> Enter this
Hope this helps
There are so many ways to apply background color in pivot table cell. Most of them these two ways are better than others.
1. Colormix Wizard...
Right Click on the pivot table > Properties > Expression tab > Edit Expression > File > Colormix Wizard... > Fill all the options values as required to you > Finish.
2. If (Condition, then, else)
as mentioned by many community members in this thread as given above.
I recommended you to do this by Colormix Wizard..., Colormix Wizard... create complex expression but you don't need to edit because it works by itself and it will be generic module for your cell color.
I hope, you really enjoy this new way to apply colors.
Thanks for your reply. I am aware of all the methods you mentioned above - Colormix as well as setting Background color using condition. However, since I want only one color based on one condition, Background color would be a better option. I tried below but does not give the desired output for my requirement. Let me know if you have some other option or any modifications to the below condition:
if([Account] = Below([Account]) and [Debit/Credit] <> Below([Debit/Credit]) and sum(Amount) <> Below(sum(Amount)),Yellow())
To get the desired output, I multiplied the amount by -1 where it was Debit(which should have been negative in the data) and then enabled Partial Sum on the Account column. I then used below expression. This highlights the row where Total is not 0 (Credit <> Debit)
if(RowNo() = 0 and sum(Amount) <> 0, Yellow())