Plotting blank values on Column Visual

[JordanPearson]

Hi all,

So I've had a look online but all of the resolutions on previous tickets don't seem to be doing the trick. 

I'm essentially trying to create a column visual which displays the total number of requests received by month, by priority level.

Y Axis - Number of requests

X Axis - Month

Legend - Ticket level 1, 2, 3, 4 & 5

Now in some months, the SUM of tickets received for some of the levels is NULL. Example:

July:

Level 1: 0

Level 2: 0

Level 3: 10

Level 4: 20

Level 5: 50

When I plot this data onto a column chart, I would like it to show Level 1 to 5 for every month, even when the value is 0 / NULL. So the chart would have data labels showing the NULL value as 0. 

Unfortunately this isn't working, the 0 values don't plot on the column chart at all, only the ones where SUM is greater than 0. I've clicked the "Show items with no data" option, and I've tried using a SUM + 0 formula, no luck. 

Is there any other methods around this?

Unfortunately this data is not coming from a database so I'm unable to manipulate the data in SQL etc, it would have to be manipulated within PBI.

[amitchandak]

@JordanPearson , Add +0 your measure and try

[JordanPearson]

Hey, thanks for this.

Unfortunately the +0 measure does nothing, the formula works fine and calculates the SUMs as normal, however doesn't show 0's on the data labels still.

[v-easonf-msft · Accepted solution]

Hello, @JordanPearson

See this related thread.

https://community.powerbi.com/t5/Desktop/bar-chart-suggestion-to-show-zero-values/td-p/555531

Power BI cannot display the zero value in some charts.

As a workaround, you can display 0.01 which is approximately equal to 0 on a chart.

You can also drag the original field to the tooltip to display it.

Best regards
Community Support Team _ Eason

[JordanPearson]

Hi! Big thanks for this.

Worked like a treat once I joined a lookup table onto it.

Cheers!