Dynamic Text Colour on Pivot Table

[surtex]

Hey Community,

I have data which can be displayed by Month or YTD. The formula for the column "Ergebnis Ist 2024 [€]" is:

IF(vYTD='1',

SUM(

{<KondArt = {'Erlös'},

Year={"$(=Right(Datumsfilter,4))"},

Datumsfilter={"<=$(=max(Datumsfilter))"}>}

[Betrag [€]]])/vDenominator

-SUM(

{<KondArt = {'Aufwand'},

Year={"$(=Right(Datumsfilter,4))"},

Datumsfilter={"<=$(=max(Datumsfilter))"}>}

[Betrag [€]]])/vDenominator,

SUM(

{<KondArt = {'Erlös'},

Year={"$(=Right(Datumsfilter,4))"},

Datumsfilter={"=$(=Left(Datumsfilter,2))"}>}

[Betrag [€]]])/vDenominator

-SUM(

{<KondArt = {'Aufwand'},

Year={"$(=Right(Datumsfilter,4))"},

Datumsfilter={"=$(=Left(Datumsfilter,2))"}>}

[Betrag [€]]])/vDenominator)

I would like a value to be displayed in red as soon as it is negative - regardless of whether I have selected MTH or YTD. However, with my formula this logic only works for either MTH or YTD:

IF(

(SUM(

{<KondArt = {'Erlös'},

Year={"$(=Right(Datumsfilter,4))"},

vYTD ={1},

Datumsfilter={"<=$(=max(Datumsfilter))"}>}

[Betrag [€]]])/vDenominator

-SUM(

{<KondArt = {'Aufwand'},

Year={"$(=Right(Datumsfilter,4))"},

vYTD ={1},

Datumsfilter={"<=$(=max(Datumsfilter))"}>}

[Betrag [€]]])/vDenominator)<0,red(),

IF(

(SUM(

{<KondArt = {'Erlös'},

Year={"$(=Right(Datumsfilter,4))"},

vYTD ={0},

Datumsfilter={"=$(=Left(Datumsfilter,2))"}>}

[Betrag [€]]])/vDenominator

-SUM(

{<KondArt = {'Aufwand'},

Year={"$(=Right(Datumsfilter,4))"},

vYTD ={0},

Datumsfilter={"=$(=Left(Datumsfilter,2))"}>}

[Betrag [€]]])/vDenominator)<0, red()))

Can you help me please?

Thanks and regards

Josh

[LRuCelver · Accepted solution]

Can you use this?

If(Column(N) < 0, Red(), Green())

N is the number of you column/measure. The 3rd measure would be Column(3). This way you don't have to recalculate everything.

Change the colors or remove the else case from the if, if needed.

[surtex]

Thanks. The formula works!!! I didn't know the command "Column()" before 😄