expression

[Francine]

Hi,  

I have a pivot table with data that includes Fiscal Yeas 2019 and 2020 (I just included 2020 data).  Last year I had a background color expression of :

if(sum([BGT OBL PLAN AMTS])/Sum([BGT AUTHORIZED]) > .92, 'green', lightred())

I would like to have that expression for 2019 but set my 2020 color expression at >.25.

Any help would be greatly appreciated.  

Source doc column header for fiscal year is: FY

Thank you! 

[Kashyap_R · Accepted solution]

Hi

Try this 

if((sum({<[Year]={'2019'}>}[BGT OBL PLAN AMTS])/Sum({<[Year]={'2019'}>}[BGT AUTHORIZED])) > .92  or

(sum({<[Year]={'2020'}>}[BGT OBL PLAN AMTS])/Sum({<[Year]={'2020'}>}[BGT AUTHORIZED])) >.25,

green(), lightred())

Hope this helps

Thanks

[Francine]

This is amazing - worked perfectly!! Thank you so much!!!

Kindest Regards
Francine

[Francine]

Hi -the expression provided as a solution for my initial question worked at first, however I adjusted the parameters to  > .98 for 2019 and > .90 in 2020 and I am not getting data retrieval.  I am getting incorrect back colors across the board.  Not sure how this can be???  Here is what I entered: 

if((sum({<[Year]={'2019'}>}[BGT OBL PLAN AMTS])/Sum({<[Year]={'2019'}>}[BGT AUTHORIZED])) >.98 or (sum({<[Year]={'2020'}>}[BGT OBL PLAN AMTS])/Sum({<[Year]={'2020'}>}[BGT AUTHORIZED])) >.90, green(), lightred())