Color conditional formatting based on column

[joannasokolowsk]

Hi all,

I would like to ask you about conditional formatting regarding table chart. Is there a way to add a background color for values from FY19 column if the value is smaller/bigger comparing to value in FY18 column. 

Thanks,

Asia

[amitchandak · Accepted solution]

@joannasokolowsk , have year measures like

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

diff = [This Year]-[Last Year ]

If date is selected

Create a color measure and use that is conditional formatting using field value option

if([diff] > 0, "green", "red")

refer for steps

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[negi007 · Accepted solution]

@joannasokolowsk 

Please follow below steps

1. Create a measure to calculate difference between two financial years. 

FY_Diff = SUM(FY_Compar[FY19])-SUM(FY_Compar[FY18])

(imp: if you do not have FY19 and FY18 as seperate columns, then you will have to create two more measures. each one to calcualte financial year values of FY18 and FY19 )

2. Then use the measure created in first step for conditionalf formatting like below

3. Your final output is below

let me know if you need more assistance in this. I hope this should resolve your concern.

[joannasokolowsk]

Thank you!

[joannasokolowsk]

Thanks for your suggestions! If I need to compare 20 such columns I guess I need to create a mesaure for all such cases, right? 

Thanks,

Asia

[v-easonf-msft · Accepted solution]

Hi, @joannasokolowsk 

Yes,the conditional format is based on some field. If you have 20 sets of such comparisons, then it is inevitable that you need 20 such  fields (column\measure) as the basis for judgment.

Best Regards,
Community Support Team _ Eason

[negi007 · Accepted solution]

@joannasokolowsk 

Please follow below steps

1. Create a measure to calculate difference between two financial years. 

FY_Diff = SUM(FY_Compar[FY19])-SUM(FY_Compar[FY18])

(imp: if you do not have FY19 and FY18 as seperate columns, then you will have to create two more measures. each one to calcualte financial year values of FY18 and FY19 )

2. Then use the measure created in first step for conditionalf formatting like below

3. Your final output is below

let me know if you need more assistance in this. I hope this should resolve your concern.

[amitchandak · Accepted solution]

@joannasokolowsk , have year measures like

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

diff = [This Year]-[Last Year ]

If date is selected

Create a color measure and use that is conditional formatting using field value option

if([diff] > 0, "green", "red")

refer for steps

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values