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
dokat
Post Prodigy
Post Prodigy

Reduce space between Y-Axis and bars in clustered bar chart

Hi,

 

I have clustered bar chart in my report and i was wondering if there is a way to reduce gap between Y-Axis and the bars. Appreciate any help. Thanks

 

dokat_0-1646020072144.png

 

1 ACCEPTED SOLUTION

Yeah, you have text in this column, so errors occur because the word can not be converted to number.   I would recommend that that you use :

new column =

if (package = "all other", "999", package).   After that, convert the new column to whole number and use this as axis..

View solution in original post

17 REPLIES 17
parry2k
Super User
Super User

@dokat if you have added a new column as @jianlong suggested, then convert that to number and use that on x-axis.

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k yes creating new column worked. Thank you

parry2k
Super User
Super User

@dokat maybe add trim/clean steps in PQ and then convert it to number:

 

parry2k_0-1646021986047.png

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k I tried the above method however it returned error. There are text format cells in the column like ("All Other") that returned error after trim/clean and change format type. to whole number. Please see below image of the data.  Not sure how can i change the format without getting error and reduce gap between Y-axis and bars.

 

dokat_0-1646024367890.png

 

Yeah, you have text in this column, so errors occur because the word can not be converted to number.   I would recommend that that you use :

new column =

if (package = "all other", "999", package).   After that, convert the new column to whole number and use this as axis..

@jianlong I created the calculated column and converted pack size value from text to number however after converting, chart format completely changed looking like below.  It is due to pack sizes range from 1 to 1440. I'd like to sort it by dollar sales so that packs with small sales pushed downt to the bottom of the chart. However i dont see an option to be able to sort the data by sales since data is converted to number format. Is there any work around this? What may caused this issue? Appreciate any help

dokat_0-1646186324062.png

 

The only way to solve this is to use the newly created column as text, instead of converting to number.  You can check the difference as the sreenshot

jianlong_0-1646188122857.png

 

@jianlong I converted back to Text format but now i am back to having same problem. where gap between bars and y-axis is too wide. I see that you don't have that problem on your chart. Are there any settings i can adjust to close that gap. Thanks

 

dokat_1-1646188379143.png

 

 

It's still the space problem then.  Try  1) Convert this new column from text to number, then convert back to text, to see if the space is still there.   2) use trim/clean to remove the space

@jianlong Yes unfortunately it is. I tried both methods and still having the same issue. What's interesting is in certain slicer selections issue dissapears. You could see below with one slicer selection gap dissapears with other one it pops back. Thanks

 

dokat_0-1646195235919.png

dokat_1-1646195261992.png

 

Well, frankly I don't know what to do now, you can post a link or your file here, so I can have a look to see what's the problem.

@jianlong Thank you. It will be difficult for me to share the file as majority of the data is confidential financial information. But thanks for looking into it.

@jianlong . Created a new column and it worked. Thank you

jianlong
Resolver I
Resolver I

Y axis,  12, 4 ... are text and there is too much "text space" before and after the number itself ?   First time to see this much space, regardless Y axis is text or number or not.

Yes it is text and it doesnt allow me to convert to a number, unless there is way you know of. Thanks

1) check to see if there is any character in packages, that might not allow you tranform the text to number;

2) use transform under query editor,  under extract, use trim and/or clean, then see if everything works out

jianlong_0-1646021795099.png

 

@jianlong Thanks for your response. Yes i tried to trim and clean and converted to whole number. The issue i came across is it returned error message for true text values in the column like "All Other" It converted numbers stored as text to number format. Thanks

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