PowerBi table | Conditional formatting per line

[lennardheyder]

Dear forum,

We are trying to visualize the change in margins per kg or prices per month by product group with a color gradients (red/yellow/green). The columns should show month.year, the lines the product groups and the average selling price as value. In Excel it is rather straight forward (individual conditional formatting per line available). In PowerBi it is more challenging as there is only conditional formatting for the total table available:

Excel:

 PowerBi:

Any idea how to implement ? The following website gave a good hint but not sufficient for the challenge:

Power BI Conditional Formatting by Rows | by Patrick Pichler | Creative Data | Medium

[lennardheyder]

Hey @Padycosmos , this is not really helpful I believe....we would need to calculate an %-value for each line based on the min / max and base a measure on this value (similiar to the linked article but for lines, not for rows)

https://medium.com/creative-data/power-bi-conditional-formatting-by-rows-8ce6187e25c6

[Padycosmos]

Would you be able to share the pbix file?

[lennardheyder]

	Feb 2022	Mar 2022	Apr 2022	May 2022	Jun 2022	Jul 2022
Product 1	1,20	1,60	1,15	1,18	1,30	0,90
Product 2	9	11	6	6	7	8
Product 3	21	30	27	19	25	22
Product 4	2,3	2,8	2,9	2,1	4	4,1

Attached a dataset, we would need to have gradient color per line, so would mean for Product 4 - 4,1 dark green,   2,1 red 

[Padycosmos]

Hope this helps:

Colour =

SWITCH(TRUE(),SELECTEDVALUE(Data[Product])="Product 4" && SELECTEDVALUE(Data[Amount])>=4,"Dark Green",

SELECTEDVALUE(Data[Product])="Product 4" &&( SELECTEDVALUE(Data[Amount])>=2.5 && SELECTEDVALUE(Data[Amount])<4),"Yellow",SELECTEDVALUE(Data[Product])="Product 4" &&( SELECTEDVALUE(Data[Amount])>=MIN(Data[Amount]) && SELECTEDVALUE(Data[Amount])<2.5),"Red")

For this to work, all your dates should be in one column and amount should be in a separate column in the table. If it is not so, in the table, you may have to unpivot the month-year columns.

If you are unfamiliar with unpivoting, you may watch this screen-recording

https://drive.google.com/file/d/1BcQg-qs5x9xhf292kcKkOI2O2kH_PQhP/view?usp=sharing

[Padycosmos]

Hope this video helps:

https://www.youtube.com/watch?v=7veYU1EeO6w