Forum Discussion
Color conditional formatting based on column
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
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-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-valuesPlease 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 below3. Your final output is below
let me know if you need more assistance in this. I hope this should resolve your concern.
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
5 Replies
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 below3. Your final output is below
let me know if you need more assistance in this. I hope this should resolve your concern.
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-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-valuesThanks 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
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
Thank you!
