Show 0 when no data in clustered column chart

[mrbartuss]

I have a clustered column chart with one filed on the X-axis and two measures. Example below:

If there's value either for Sales or Target, but not for both measures I would like to show 0 instead of nothing (e.g. 0 for Banana target and 0 for Plum Sales).

What I've tried so far:

1) Sales Target = SUM ( 'Table'[Target] ) + 0

2) Sales Target = COALESCE ( SUM ( 'Table'[Target] ), 0 )
But then I achieve this:

I do not want to show values on the X-axis where both measures have no data (e.g. Orange, Pineapple).

Unfortunately "Show blank as" 0 under Data labels Value setting also does not work

Any insights would be appreciated

[Anonymous]

Hi @mrbartuss 

Thank you very much sevenhills for your prompt reply.

It seems that the method provided by sevenhills is suitable for your situation. Can you tell me if your problem is solved? If yes, please accept it as solution.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mrbartuss]

Unfortunately, it solved my problem only partially 

[sevenhills]

Try this, 

add a measure

Is Zero Check = IF( [Sales Target] = 0 && [Total Sales] = 0, 1, BLANK())

Expand or view filters pane

Drag this measure and set the filter as "Is Zero Check" is blank.

You get the ouput as below:

Hope it helps!

[mrbartuss]

Thank you! It works, but it seems 0 has to be added to the Sales and Target measures. They are also used on other pages and visuals. As measure cannot be added as a report or even page level filter I would have to use it on all the visuals. Are there any better solutions just for the chart?