Dynamic Text Colour on Pivot Table

surtex — Thu, 07 Mar 2024 09:06:41 GMT

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

Re: Dynamic Text Colour on Pivot Table

LRuCelver — Thu, 07 Mar 2024 13:21:30 GMT

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.

Re: Dynamic Text Colour on Pivot Table

surtex — Thu, 07 Mar 2024 13:54:55 GMT

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

topic Dynamic Text Colour on Pivot Table in App Development

Dynamic Text Colour on Pivot Table

Re: Dynamic Text Colour on Pivot Table

Re: Dynamic Text Colour on Pivot Table