cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
toniacheung
Frequent Visitor

broken line with no data on line & stacked column chart

powerbi question.png

 

I have a line and stacked bar chart for months over the course of a year.  There are some months where the column value is zero, so by default Power BI removes those columns.  I wanted to have all the months on teh chart, so selected to show data with no value, but now the lines on the chart are broken.  Is there any way to get Power BI to connect them?

 

 

 

 

 

 

5 REPLIES 5
evandrocunico Resolver III
Resolver III

Re: broken line with no data on line & stacked column chart

HI @toniacheung maybe set zero to measure instead blank.

 

New measure  = if(measure is blank();0;measure)

 

regards.

mussaenda Solution Sage
Solution Sage

Re: broken line with no data on line & stacked column chart

At the end of your measure code, put +0 to avoid broken lines

toniacheung
Frequent Visitor

Re: broken line with no data on line & stacked column chart

powerbi question2.png

 

Both solutions--the new measure and just adding +0 at the end of my formula--worked to make a continuous line.  However, I want it to ignore the 0s.  The yellow line is supposed to be a target.  The formula (after adding the +0 to keep the line connected) is MAX([column with target values for different departments])+0.  This should stay consistent over the entire year, not drop to 0 in those months.  The red line is supposed to be cumulative number over the year.  The formula for it is CALCULATE(COUNTA('Table'[Date], FILTER(ALL('Table', ISONORAFTER('Table'[Date], MAX('Table'[Date], DESC)))+0.  Again, since it is cumulative, the line shouldn't drop to 0 when we have no data for a month, it should just stay consistent until we have new value to add on to the running total.  How do I get that?

evandrocunico Resolver III
Resolver III

Re: broken line with no data on line & stacked column chart

toniacheung
Frequent Visitor

Re: broken line with no data on line & stacked column chart

Thank, my cumulative sum line formula that I put above works fine except when I have months where the value is 0.  It's not a sum, it's a count of entries.  I just need it to ignore when a month has 0 entries and connect the previous month to the next without dipping down to 0.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors