Conditional background formatting

[Anonymous]

I'm new to Power BI, so it might be that I missed somthing obvious, thank you for your help! 🙂

I have a matrix where I want to display revenue per customer and apply conditional background formatting per row. This to make it visually easy to see per customer what the trends are.

About the dataset, filtered per year:
Rows: customer name

Column: invoice date (13 colums, one for each month + total)
Value: sum(invoice amount)

Example data:

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
Customer X	4	4	6	7	5	5	5	5	6	8	7	7	69
Customer Y	2	1	2	2	1	2	3	4	3	4	4	3	31
Customer Z	3	2	2	1	2	3	2	3	2	1	3	2	26

Given the above example, that gives:

	Min	Median	Max
Customer X	4	5.5	8
Customer Y	1	2.5	4
Customer Z	1	2	3

How can I use conditional formating with a gradient background color based on the Min, Median, Max per row? (to clearify, I don't want to use the Maximum of the whole table, only releative to that row (customer), etc).

Minimum color: lowest revenue month of that customer (row)
Center color: median of the monthly revenue for the total shown period for that customer (row)
Maximum color: lowest revenue month of that customer (row)

How can I achieve this?

[parry2k]

@Anonymous thanks for explaining. One thing that took me off was your original data is different than the last reply you posted and the factor was not getting calculated the same as you have shown in the image. Took me a bit to understand why my numbers are not matching.

Anyhow, all the measures are in the attached file, you can tweak them as you see fit. 

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

@parry2k thank you for your response!

Not sure if I was clear, I want the background colour to be based on the rows. Let me try to reprhase: 

I want the MIN and MAX to of the gradient to use the MIN and MAX of each row. Not the whole table.

Right now, the gradient would take MIN: 1 and MAX: 8 to colour all the cells in the table. Where I would like that row 2 (Customer X) uses MIN 4 and MAX 8 to set the gradient colour. And for row 3 (Customer Y) to use MIN 1 and MAX 4

This is an example of how I use it right now:

As you can see, H2 and L2 are dark red, because there is a significant drop (4 points).
I2, H4, and L4 are dark blue, because there is asignificant gain. For Customer X it is a gain of 4, and for Customer Z it is 2 points.

[parry2k]

@Anonymous afaik It is not possible to use gradient colors on the columns, it is always on the rows. I don't think there is a solution to use gradient colors on the columns.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.