Relative Text Colour

[vivianjames]

Hi

Not sure if what I'm asking is possible as I am quite new. 

I want to format the text colour of a pivot table but I want the colour to be dependant on other figures in the pivot table.

I have included a picture of my pivot to hopefully help with clarity of my question.

I want column 'enrichment' and rows 'target' to show either red or green.

I want 'target enrichment' to show green if it is greater than 'control enrichment' or red if it is less than 'control enrichment' 

As you can see from the picture all I've managed is to get the font to show green if greater than 0 but this is not the desired outcome. 

Many thanks! 

[MadiF]

Hi @vivianjames  Try this logic in the 'text color expression' section for your Enrichment metric:

if("Target Enrichment">="Control Enrichment", Green(),
if("Target Enrichment"<"Control Enrichment", Red()))

In that logic I assume your field names are simply target enrichment and control enrichment. Hope it helps!

[vivianjames]

Thank you @MadiF for your reply! 

I just tried this and it tells me there is an error in the expression. Likely because those aren't exactly my field names really 

So I have 'cell_group' as the field that contains either 'target' or control' as fields and enrichment is 'net_enrichment' 

So I've now tried the below however that hasn't worked either: 

if(("cell_group"='Target')>=("CELL_GROUP" = 'Control'), Green(),
if(("cell_group"='Target')<("CELL_GROUP"='Control'), Red()))

Is there any other information I can provide to help with this?

[MadiF]

@vivianjames  I think your solution then lies in set analysis- if you could add to this chain a small sample data set of what your fields are and what they contain (in a mock table), as well as the goal result from the example, I can provide a more specific response 🙂

[vivianjames]

FIELD	WHAT IT IS	EXAMPLE
CONT_MKTG_ACT_CODE	identifier code	DR000001325
CELL_GROUP	TARGET/CONTROL	Target or Control
Vol	Total volume of contacts	285,000
Upgrade_Vol	Total volume of upgrades	6,000
NET_ENRICHMENT	£ enrichment	£22
TOTAL_SARC	Another monetary value I track	-£254
FIN_CCV	Another monetary value I track	£344
UPG_CCV	Another monetary value I track	£345
HOW I WOULD LIKE TO DISPLAY IT:
	Vol	Upgrade_Vol	ENRICHMENT	SARC	FINANCCV	MRKTGCCV	Conversion
DR100000XXX	284,526	2,767	£6	-£117	£118	£160
Control	28,503	178	£6	-£128	£164	£158	0.8%
Target	256,023	2,589	£8	-£116	£115	£160	0.2%
DR100000XXX
Control	28,503	178	£6	-£128	£164	£158	0.6%
Target	256,023	2,589	£4	-£145	£210	£110	1.7%
DR100000XXX
Control	28,503	178	£6	-£128	£164	£158	0.6%
Target	256,023	2,589	£6	-£128	£164	£158	0.6%
CALCULATIONS FOR EACH COLUMN			net_enrichment/upgrade_vol	TOTAL_SARC/Upgrade_Vol	FIN_CCV /Upgrade_Vol	UPG_CCV/Upgrade_Vol	Upgrade_Vol/Vol

[vivianjames]

@MadiF  - thank you! I hope the above is helpful? Let me know if there's anything else needed 

[MadiF]

@vivianjames That Helps! After seeing that, try this formula:
If(sum({<"CELL_GROUP"={'Target'}>}"NET_ENRICHMENT")>=sum({<"CELL_GROUP"={'Control'}>}"NET_ENRICHMENT"), green(),
If(sum({<"CELL_GROUP"={'Target'}>}'NET_ENRICHMENT')<sum({<"CELL_GROUP"={'Control'}>}"NET_ENRICHMENT"), red()))

[MadiF]

@vivianjames  Were you able to solve your problem with the above logic?

[vivianjames]

Hi sorry I had to pause this while working on something else. The font colours did change to red/green however I think there's perhaps something more complicated needed as they didn't show as I expected i.e. there were some control that were incorrectly showing one colour and same with target