cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

Highlighted
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
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors