How to give conditional Formatting to Matrix with data bars or background colors for time trend?

[Manas]

How to give conditional Formatting to Matrix with data bars or background colours for time trends?

For eg., the following image shows Bike sales for 4 years. Need to have colour data bars /background colours to see the trend whether increasing or decreasing based on each year's sales amount.

[Anonymous · Accepted solution]

Hi @Manas ,

Is this how you want it?
You can use the measure given for the conditional formatting of cell color. 

Cell Color = 
var a = SUM('Table'[Value])
var b = CALCULATE(SUM('Table'[Value]),SAMEPERIODLASTYEAR('Calendar'[Date]))
return
   IF(
       a>b,"#b3ffd9",IF(ISBLANK(a) || ISBLANK(b),"#ffffff","#ffb3b3")
   )

Go to conditional formatting of value, select background colour, then select Field Value and select this measure.

Attached .pbix for reference.
Manas.pbix

Regards,
Aditya

[amitchandak]

@Manas , Create a YOY measures, Prefer to use a separate date/year table

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

use this diff in conditional formatting , rule

or create a color measure and use in conditional formatting using field value

color = if([diff]>0, "Green", "red")

How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/...

https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pan...