Cell should mark itself if difference is higher than 0,5 to previous cell

[Anonymous]

Hi dear community,

I have a column in which my values are listed. Nothing special, just like this:

1
2
2.3
1.9
1.3

What I want to accomplish is, that the cells will e.g. turn red if the difference between them is higher than 0.5, like this:

1
2██████
2.3
1.9
1.3█████
1.1
1.7█████
1.3

Please excuse the poor depiction. If it's easier, it can also be a marking in a cell in the next colum.

Thank you very much for your time!

Kind Regards

[DataNinja777 · Accepted solution]

Hi @Anonymous ,

You can achieve your required output in many different ways, but since your numerical column has numbers which are repetitive and not in numerical order, and the data table in Power BI does not store the order as it is in your column I've added an index column using Power Query to make things easier in terms of respecting your original data order without aggregation.  

Then, I've added a column next to your original column to indicate the previous row value.

Then, I've added a simple calculated column to indicate the difference with the previous column larger than 0.5 in absolute value as indicated in your example.  

Lastly, I've written a color measure to be used in the background color for orignal column.  

I attach an example pbix file.  

Best regards,

[ThxAlot · Accepted solution]

What you need are only 2 simple things:

1. Index column for keep the orders of your original dataset;
2. a DAX measure for background color

[DataNinja777]

Hi @ThxAlot 

Good idea 😀  How did you manage to hide index column from your final output as shown below?  

Best regards,

[ThxAlot]

Hello, @DataNinja777 , you can toggle off "Text wrap" for both "Values" and "Column headers"; then manually drag "Index" width to hide it.

[DataNinja777 · Accepted solution]

Hi @Anonymous ,

You can achieve your required output in many different ways, but since your numerical column has numbers which are repetitive and not in numerical order, and the data table in Power BI does not store the order as it is in your column I've added an index column using Power Query to make things easier in terms of respecting your original data order without aggregation.  

Then, I've added a column next to your original column to indicate the previous row value.

Then, I've added a simple calculated column to indicate the difference with the previous column larger than 0.5 in absolute value as indicated in your example.  

Lastly, I've written a color measure to be used in the background color for orignal column.  

I attach an example pbix file.  

Best regards,