Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
YVR
Frequent Visitor

Conditional formatting of bar chart colors using a gradient that is a % variance from target

Hello,

 

The screenshot below is from Tableau.  How do I build this using Power BI?

Let's say:

a) the bars are Revenue for various categories. 

b) The grey vertical line is the Month-to-Date Target. 

c)  The thick, black vertical line is the Monthly Target.  I want this reference line to be displayed as well.

d) The colour of the bars is a gradient.  The darker the colour (either dark red or dark blue), the greater the % variance from the MTD target.  White is the colour for the centre at 0% variance.

 

If I only display one target, I was able to find a workaround in PBI by using the line and clustered column chart.  I changed the line, which is the Target, to have 0px, then used a marker of a line to act as the reference line.  The colour of the columns would be a gradient based on a calculation of % variance.  (With this workaround though, I do not know how to add a colour legend or change the orientation to a bar chart rather than a column chart.)

 

But most crucially, how do I show 2 target lines, including the Monthly Target one? Is there a viz or custom viz bullet chart that I should use instead? (Not sure my company will pay for it if it's premium.) Has anyone tried this? Thanks

 

Tableau_Gradient colours to Target.jpg

3 REPLIES 3
Walter_W2022
Resolver II
Resolver II

Hi @YVR , in Power BI, you can use either Charticulator or Deneb to create bullet chart, I worked on a smilar project same with yours, I am not sure whether you can share your data with me, even you can make up the data for demo purpose, so I can create bullet chart for you using Deneb or Charticulator, hope this will help.

Cheers

YVR_0-1680589282157.png

 

Hi @wangbt89 Sorry for the delay.  I didn't have access to publish the sample workbook to the web (public) and my permissions are turned off to share a link on OneDrive with anyone unless I have their email address.  So here's some dummy data.  Even with the line and clustered column chart workaround that I had mentioned in the original post, I still have some issues:

a) I am unable to change the orientation so that the bars are horizontal instead of vertical, so if your Category names are long, or you just need to have a landscape orientation to fit with the overall layout of your report, you're stuck.

b) I don't know how to add a colour gradient legend here (I can do a workaround for this by creating my own legend with shapes and textboxes, I guess.)

c) Most importantly, I am unable to display another reference line to indicate the Monthly Target as well.
Ideally, I'm looking for a solution that doesn't involve downloading a custom viz that's not PBI Certified or has a premium cost as my company may not allow us to import those.  

Thanks for your help!

@YVR , as I said before, all your requests can be done using Charticulator or Deneb in Power BI, they are all free and certified by Microsoft, you have to do some research, good luck!!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.