Forum Discussion

mrbartuss's avatar
mrbartuss
Icon for Advocate II rankAdvocate II
1 year ago

Show 0 when no data in clustered column chart

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
Need Help

4 Replies

  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      mrbartuss
      Icon for Advocate II rankAdvocate II
      1 year ago

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      mrbartuss
      Icon for Advocate II rankAdvocate II
      1 year ago

      Unfortunately, it solved my problem only partially