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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors