Format Cells showing difference in value

[Meadsie]

Hi all,

I have a problem that I cant work out how to solve.

I have a matrix consisting of 5 columns (B-F) and 5 rows (1-5)

Each of the cells in the matix contains a figure. 

for example Cell B3 = 100, Cell C3 = 150, Cell D3 = 120

I want to highlight the cells where the next cell value has gone up or down. If it has gone up colour the cell green, If it has gone down colour the cell red. any ideas please ?

So in teh example above, C3 would be GREEN, D3 would be RED

Any help would be greatly received.

[v-frfei-msft]

Hi @Meadsie,

Does this make sense? If yes, kindly mark the answer as solution please. Thanks a lot.

Regards,
Frank

[v-frfei-msft · Accepted solution]

Hi @Meadsie,

If your original table is like this one:

Based on my test, we can take the following steps to work around to meet your requirement.

1.Add a custom column based on the table using the formula:

if  [type]= "B" then 1 else if [type] = "C" then 2 else if [type] = "D" then 3 else if [type] = "E" then 4 else 5

2.Create a column using the formula as below:

diff = var prevalue = CALCULATE(FIRSTNONBLANK(Table2[Column1],1),
FILTER(Table2,Table2[Cate]= EARLIER(Table2[Cate]) && (Table2[Custom]= EARLIER(Table2[Custom]))-1))
return
IF(ISBLANK(prevalue), BLANK(),Table2[Column1]-prevalue)

Then we can create the Matrix and set the font color as the picture below:

For more details, please check the pbxi as attached.

https://www.dropbox.com/s/yjanow0xat2aj9m/Format%20Cells%20showing%20difference%20in%20value5.pbix?d...

Regards,

Frank