Need only whole numbers in vertical axis of Dashboard chart widget

SASardy

I am tracking items that should only ever be between 0 and 10 at the most, however when they are plotted on my dashboard widget (see below), it shows 0.2, 0.4, 1.2, etc. These will only ever be whole numbers (0-10) and are pulled from a count on my main sheet. How can I remove the non-whole numbers?

Mark.poole

What happens when you select scale to data values on the vertical axis?

SASardy

@Mark.poole That is already checked. Unchecking it makes no difference.

Mark.poole

The way the bar charts work is based of the highest number on the bar. So in this case if 1 is the only number on it. It will show Decimal points. It should disappear once you get numbers ranging from 1-10 on the bar chart.

SASardy

@Mark.poole But there is no way to force that change? There will be times when there is only 1 item being tracked and it just looks sloppy to me to have decimals on a chart where a fraction of an item is not possible because everything is counted in whole numbers.

Mark.poole

I honestly wouldn't recommend using a bar chart for a single line item. What's your thoughts on using a metric instead? Unless there is a specific reason why you use the bar chart. As far as a way to force it. I am honestly not sure. Maybe @Genevieve P., @Paul Newcome or @Andrée Starå could better answer if it could be forced.

SASardy

@Mark.poole I am using a bar chart because there are 3 categories I am tracking. I am basically tracking and visually representing tasks based on time until due (so, due in 90+ days, 31 - 90 days, 30 or less days). Each bucket can realistically have between 0 - 10 items each and are color coded green, yellow, and red for easy identification. Any other suggestions to represent that data is welcome. As I said before, this just looks sloppy to me.

Mark.poole

@SASardy Would a Pie chart work better for you. I normally wouldn't recommend one if its not %. What it would do is just not show an entry if it is 0. Also what formula are you using to pull your numbers for your report widget? a Countif()? If your comfortable sharing your screen. Or are able to make dummy data that I could work with. I would hapily do a teams or zoom meeting with you to help you out.

SASardy

I have tried the pie chart and it will not color code based on time (ie. when I switch to pie chart, it gives me a pie with 1 green item when that item should be red). And yes, I am using a countif formula to pull numbers into a report. I am unable to share the data but I can try to work up some dummy data to share.

Paul Newcome

There are two ways to get rid of the decimals. The first is to adjust the size of the widget. The second is to add a 4th bar, change the color to white and don't show the count, and hard code it to be some number that when charted pushes the scale to whole numbers only.

SASardy

I read in the forums to try resizing and I have done that with no change. I resized it to nearly the size of the screen and the decimals remained. I will give your other suggestion a try. Thank you.

Paul Newcome

Resizing to get rid of decimals would mean making it smaller, but really only having 1 is going to mean you have to go REALLY small before the decimals disappear.

Genevieve P.

Thanks for the tag, @Mark Poole

I agree with @Paul Newcome! Chart Y axis will automatically adjust based on the Min/Max numbers being displayed.

You could potentially chart the negative value (e.g. 1 task done, 9 tasks in progress = a total of 10 in a stacked bar) to push out the Y value. For example:

So you can have a chart like this:

Cheers,
Genevieve

Paul Newcome

@Genevieve P. I like your idea better of using the stacked bar.

SASardy

Well the stacked bar will not work for me because the 1-10 total tasks at any given time is the entirety of the tasks I am tracking. Currently, I only have 1 task I am tracking so there is no negative value to track but I do appreciate the suggestions. Sounds like I am just running into a limitation of the product which is not the first time that has happened nor will it be the last, I am sure. Again, thanks for the input!

Paul Newcome

@SASardy The stacked column should work though. You would use a second column (or row depending on your existing structure) and do the formula similar to @Genevieve P.'s formula to create a second data set that when added would equal 10 for each of the categories. Based on the screenshot in the original post, your data set would look along the lines of

0…..10

0…..10

1…..9

Then adjust the series represented by the second set of numbers to not show counts and the color is white.