Expression background color vs Custom cell format

[sibrulotte]

Hi,

I have an expression background formula that basically says if you're under budget by 250K and that is 10% of budget then put in lightgreen, if false but you're over budget by 50K and that is 10% of budget then you're in a red color.

see this:

=if((sum({$<[%CLE_Type_Transaction_ID] -= {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * -1)-

sum({$<[%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * -1)> 250000) and

(sum({$<[%CLE_Type_Transaction_ID] -= {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * -1)-

sum({$<[%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * -1)>

0.1*(sum({$<[%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * %CR004M))),lightgreen(),

if((sum({$<[%CLE_Type_Transaction_ID] -= {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * -1)-

sum({$<[%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * -1)< -50000) and

(sum({$<[%CLE_Type_Transaction_ID] -= {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * -1)-

sum({$<[%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * -1)<

-0.1*(sum({$<[%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * %CR004M))), RGB(255,87,129)))

Works like a charm for all my rows except the last total line. It just free games on me

I try to fix the background color with a custom cell format, but the expression background color takes precedence.

Any clue why this would happen?

[marcus_sommer]

I think the reason is that your expression on these TOTAL level returned no valid result. Probably you will need to wrap your expression into an aggr-function to get your desired result: AGGR...

- Marcus

[sibrulotte]

Thank you for the hint.

I started a new expression to validate the proper AGGR nomenclature to obtain the desired effect.

With an AGGR on the three dimensions I am showing, I get a result for each line but blanks for totals.

So that's not working out for me right now.