Forum Discussion
Conditional formatting for difference between bars?
Hi,
Essentially I'm building a headcount report, and i have a table (by month) showing our cumulative attrition against a target attrition rate. I have conditional formatting on the table if attrition is above target.
I was hoping to express this conditional in a visualisation too, to express any large deviation from the target. Is there any custom visual or any way to show what I want? Thanks for any help!!
Alsoo all my values are measures i can use
Hi kowas2,
Check out below solution which is a workaround as directly adding Cumulative Attrition % and Target % to the clustered column chart will not enable conditional formatting or can show conditional formatting for only diff%.
Create two measures - Attrition % (Less) and Attrition % (More) - It helps to show less % bar as green and more % bar as red.
Attrition (Less) = IF(SUM(Table1[Cumulative Attrition %])<= SUM(Table1[Target %]), SUM(Table1[Cumulative Attrition %]))Attrition (More) = IF(SUM(Table1[Cumulative Attrition %])> SUM(Table1[Target %]), SUM(Table1[Cumulative Attrition %]))Add these two measures along with Target % to clustered chart.
Go to Visual Formatting > Columns > Choose Series > Choose each field and select colour for each of them.
Check below example for Attrition Less
Then, in Columns > Choose Series - All > Layout > Modify changes as below to align the bars accordingly.
Result -
💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics Engineer
3 Replies
Hi kowas2,
Check out below solution which is a workaround as directly adding Cumulative Attrition % and Target % to the clustered column chart will not enable conditional formatting or can show conditional formatting for only diff%.
Create two measures - Attrition % (Less) and Attrition % (More) - It helps to show less % bar as green and more % bar as red.
Attrition (Less) = IF(SUM(Table1[Cumulative Attrition %])<= SUM(Table1[Target %]), SUM(Table1[Cumulative Attrition %]))Attrition (More) = IF(SUM(Table1[Cumulative Attrition %])> SUM(Table1[Target %]), SUM(Table1[Cumulative Attrition %]))Add these two measures along with Target % to clustered chart.
Go to Visual Formatting > Columns > Choose Series > Choose each field and select colour for each of them.
Check below example for Attrition Less
Then, in Columns > Choose Series - All > Layout > Modify changes as below to align the bars accordingly.
Result -
💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics Engineerthats such a cool workaround!! thank youu 💕💕
Nice Workaround!👏
