Problem
I need to use the dynamic conditional formatting built-in in Power BI to change color row-wise. The lowest value in the row must be green, the highest value must be red and all colors in the middle are shades of orange between green and red. The problem is that built-in conditional formatting calculates maximum and minimum over the entire matrix and there is no option to calculate maximum and minimum column-wise or row-wise.
Example
Below a simple example, given dimension_1 (Color), dimension_2 (Year) and a KPI (measure). In reality, the matrix is much bigger than this (e.g. 15 distinct year) but this is a representative example.
+-------------+-------------+------+------+------+
| dimension_1 | dimension_2 | 2017 | 2018 | 2019 |
+-------------+-------------+------+------+------+
| Red | Measure | 0 | 50 | 100 |
| Blue | Measure | -100 | -50 | 0 |
| Green | Measure | -100 | 0 | 100 |
+-------------+-------------+------+------+------+
Power BI would consider -100 as minimum value and +100 maximum value. Hence, the conditional formatting applied would be like:
+-------------+-------------+--------------+--------------+--------------+
| dimension_1 | dimension_2 | 2017 | 2018 | 2019 |
+-------------+-------------+--------------+--------------+--------------+
| Red | Measure | light orange | dark orange | red |
| Blue | Measure | green | dark orange | yellow |
| Green | Measure | green | yellow | red |
+-------------+-------------+--------------+--------------+--------------+
Expected solution
The expected solution apply conditional formatting calculating maximum and minimum row-wise. Therefore, in row 1 the minimum value is 0 and the maximum value is 100. If we replicate this calculation for all rows we obtain the following:
+-------------+-------------+-------+--------+------+
| dimension_1 | dimension_2 | 2017 | 2018 | 2019 |
+-------------+-------------+-------+--------+------+
| Red | Measure | green | orange | red |
| Blue | Measure | green | orange | red |
| Green | Measure | green | orange | red |
+-------------+-------------+-------+--------+------+
Reminder
The conditional formatting must be dynamic given maximum and minimum value row-wise. In other words, I do no not want to use rule to define fixed colors, instead let Power BI define different shades.
