8

I have a table with sparse values, but in my line chart I'd like the line to go back to zero when there's no data to show. e.g:

instead of:

enter image description here

I want:

enter image description here

To produce the first graph I used a generated table with a series of values for the x-axis, and the sparse data table was joined onto that generated table to provide the values.

In some cases there's a summation of the values, which show with the line above zero. The values that appear low down to the axis are in fact just very small values from my data, they are not zeros.

Selecting "Show items with no data" on the axis doesn't seem to do what I want, which other answers I've found are suggesting. Is there something I'm missing?

I would like to find out what options I need to use, or if there's some kind of trick with the data table I should implement, which would give me zeros on the line graph (as shown in the second image) when there is no data found.

Dan Rayson
  • 1,315
  • 1
  • 14
  • 37

2 Answers2

12

Ryan provides a great answer for handling this in the Power Query editor. If you (or someone with your same question) can't edit the queries, here is a couple of options for solving this issue in DAX and visual settings.

One thing I want to note about Ryan's answer is "null" and "zero" are drastically different items. A "null" means no data and a "zero" means 0. Depending on what kind of data you're looking at, treating those as the same could lead to misinformation.

Solutions:

  1. If your X axis is "Categorical", you can use the "Show items with no data". At which point any value on your X axis that has no data will be shown as a gap in the line.

    This would be my preferred method to show items with no data.

Categorical X

Result

  1. If your X axis is "Continuous" and/or you really want to show zeros, simply create a measure with the following formula to turn all blanks into zeros.

Cont X

This is what the line chart would look like with a normal measure.

Old

And this is what it looks like using the new measure.

New

And here is the measure formula that gives the behavior in the last picture.

    Total Amount = IF(ISBLANK(SUM(Table1[Amount])), 0, SUM(Table1[Amount]))
Community
  • 1
  • 1
Joe G
  • 1,726
  • 1
  • 8
  • 15
  • Yeah, a categorical line works for months, but if you are truly talking about categories, a bar chart is definitely better. And speaking of deciding what kind of chart to use, there is a [neat flowchart](http://www.infographicsblog.com/wp-content/uploads/2011/11/chart-suggestion-infographic.jpg) to help. It's not perfect, but is a good starter. – Joe G Apr 03 '19 at 21:32
  • Thank you! Number 2 solved it for me, so easy when you know how! Great answer too, very easy to follow. – Dan Rayson Apr 04 '19 at 09:10
  • It looks like the categorical/continuous dropdown selection has been removed by Power BI. @JoeGravelyn does that option still appear for you? – Susie Derkins Jul 26 '23 at 16:14
  • 1
    @SusieDerkins It does if certain conditions are met. If you are using a field that is completely numeric, it should still have the option between continuous and categorical. If your field has any non-numeric data, it won't give the option because it has to be categorical. – Joe G Aug 01 '23 at 04:33
0

It seems like you've got a lot done when you did the 'generate table' step for your x-axis values. I'm assuming this means you have a lot of [x-value/null] combinations in the resulting table if you did a left outer join (if you didn't, then go back and use an outer join!) to your 'sparse data'. We need these to be [x-value/0].

In power query, you can just right click the column header, choose 'replace values' and switch null for 0.

That should do it.

So, a good rule of thumb is "never trust a null." If you mean Zero, then use Zero.

Ryan B.
  • 3,575
  • 2
  • 20
  • 26