Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tomgrindey
New Member

Line chart of sales data not showing zero when there is no data

Hi, 

 

Please bear with me as I am very new to Power BI

 

I have a line chart based on a table of data of sales by month. When there is no sales in that month the line chart doesn't drop to zero. Is there a simple way to make the chart drop to zero when there is no data for a month?

 

Thanks, 

Tom

1 ACCEPTED SOLUTION

@tomgrindey 

 

Here is the measure I'm using in my example:

Sum Forecast = SUM('Data Table'[Forecast]) +0

So, try adding the "+0" to your measure as above and see if it solves the problem.

If it doesn't, please post a screenshot and let us know where the field for the x axis (Month Year) is coming from (Data table? Calendar Table?)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

7 REPLIES 7
Gordonlilj
Solution Sage
Solution Sage

Hi,

 

Using a measure you could replace blank values with 0 like the example code below

IF(ISBLANK(SUM('Table'[Sales])),0,SUM('Table'[Sales]))

 or maybe

SUM('Table'[Sales]) +0

 

Hi, 

 

Sorry if i'm beng daft. My actual data doesn't have any blank data, the line chart is coming from a visual table? If this solution still works where do I apply the measure?

 

Thanks, 

Tom

@tomgrindey 

 

 If  your chart looking like this....

chart blanks.JPG

 

then the trick suggested by @Gordonlilj  of including " + 0 " at the end of your measure will deliver this:

chart.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi, 

 

The chart looks like the below:

 

Screenshot 2020-02-07 at 11.24.00.png

 

It comes from the below visual:

Screenshot 2020-02-07 at 11.25.35.png

 

These are just samples of the visuals. There is data by month to 2020. 

 

Thanks, 

Tom

@tomgrindey 

What measure are you using for the values in the chart?

 

BTW, the values in the x axis can be misleading. This is the chart I showed above without adding the "+0" to the measure and without the option of "showing items with no data" in the dropdown of the x Axis field:

missing months.JPG

The months with no data are simply omitted, creating a continuous line. Adding the "+0" solve this as there are values (including 0) in all months

 

PS:; are you using a column from a calendar table as your x axis?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi @PaulDBrown 

 

I hope this is the right thing - I am using a SUM on the QTY. 

 

Thanks, 

Tom

@tomgrindey 

 

Here is the measure I'm using in my example:

Sum Forecast = SUM('Data Table'[Forecast]) +0

So, try adding the "+0" to your measure as above and see if it solves the problem.

If it doesn't, please post a screenshot and let us know where the field for the x axis (Month Year) is coming from (Data table? Calendar Table?)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.