Dynamic coloring in pivot table

[Costa]

Hey,

I have coloring problem and I hope someone can find the problem.

What i want to achieve is, that positive value are colored, based on their rank from a light green to a dark green, while negative figures are colored from a light red to a dark red, based on their rank (zero = white).

Now, this pivot table is based on an unbalanced hierarchy. In order to prevent collapsing levels that are empty, I needed to bring in some logics.

I took the same calculation logic into the colormix1 (rank..)) function for the background coloring.

Unfortunatly it does not really work. Negative values are red and positives green, but there is no real color grading.

I use the following coding - ich believe the problem roots from the 'noofrows(total)', but i can't fix it:

If ( LVL1='Market Performance' and Dimensionality()='1' or
LVL1='Profit contribution' and Dimensionality()='1'or
LVL1='Operative Income I' and Dimensionality()='1'or
LVL1='Operative Income II'and Dimensionality()='1' or
LVL1='Operative Income III' and Dimensionality()='1'or
LVL1='Operative Income IV'and Dimensionality()='1'
, rgb(0,37,62)
, if ( if ( not ( ( Isnull(LVL6) and dimensionality()=6 ) or
( Isnull(LVL5) and dimensionality()=5 ) or
( Isnull(LVL4) and dimensionality()=4 ) or
( Isnull(LVL3) and dimensionality()=3 ) or
( Isnull(LVL2) and dimensionality()=2 ) or
( Isnull(LVL1) and dimensionality()=1 )  
)
, RangeSum ( Sum({$<Table_ID={'PnL_internal'},PnL_Class={'cost'},$(vFY_BU_Flow_CurrentYear_Expression_vCurrency2)>}Kennzahl*-1/1000000)
, Sum({$<Table_ID={'PnL_internal'},PnL_Class={'earning'},$(vFY_BU_Flow_CurrentYear_Expression_vCurrency2)>}Kennzahl/1000000))

) <= 0
, ColorMix1 ( Rank ( total if ( not ( ( Isnull(LVL6) and dimensionality()=6 ) or
( Isnull(LVL5) and dimensionality()=5 ) or
( Isnull(LVL4) and dimensionality()=4 ) or
( Isnull(LVL3) and dimensionality()=3 ) or
( Isnull(LVL2) and dimensionality()=2 ) or
( Isnull(LVL1) and dimensionality()=1 )  
)
, RangeSum ( Sum({$<Table_ID={'PnL_internal'},PnL_Class={'cost'},$(vFY_BU_Flow_CurrentYear_Expression_vCurrency2)>}Kennzahl*-1/1000000)
, Sum({$<Table_ID={'PnL_internal'},PnL_Class={'earning'},$(vFY_BU_Flow_CurrentYear_Expression_vCurrency2)>}Kennzahl/1000000))

)
) / NoOfRows (total) /* NoOfRows (total) */
, rgb(255,255,255)
, rgb(255,73,99)
)
, ColorMix1 ( Rank ( total if ( not ( ( Isnull(LVL6) and dimensionality()=6 ) or
( Isnull(LVL5) and dimensionality()=5 ) or
( Isnull(LVL4) and dimensionality()=4 ) or
( Isnull(LVL3) and dimensionality()=3 ) or
( Isnull(LVL2) and dimensionality()=2 ) or
( Isnull(LVL1) and dimensionality()=1 )  
)
, RangeSum ( Sum({$<Table_ID={'PnL_internal'},PnL_Class={'cost'},$(vFY_BU_Flow_CurrentYear_Expression_vCurrency2)>}Kennzahl*-1/1000000)
, Sum({$<Table_ID={'PnL_internal'},PnL_Class={'earning'},$(vFY_BU_Flow_CurrentYear_Expression_vCurrency2)>}Kennzahl/1000000))

)
) / NoOfRows (total)
, rgb(5,206,159)
, rgb(218,254,245)
)
)
) 

can anyone help?

Thanks and Best

[Costa]

probably easier to read 🙂