Fill gaps in Line chart

[Maria123]

Hi!

I have some troubles. Lets say that I have some information per sales week. I have two type of product categories, A & B. A is shown as column values and B is shown as line values. Some weeks we havn't had any sales for Product B, only for A. When I have no sales (blank) then the line only show markers instead of a contious line. I have used the line and clustered column chart.

Is there anyway to say that if it is blank then continue the line anyway?

Example

Product A

Sales week 2017-25, margin 10%

Sales week 2017-26, margin 30%

Sales week 2017-27, margin 35%

Product B

Sales week 2017-25, margin 35%

Sales week 2017-27, margin 25%

Right now the chart shows 25 & 27 as markers but I want a line that connects the markers of 25 & 27, in other word, it will be at 30% for week 26 (but you will not see it as a value).

In excel I have the same chart but there I have a formula that says if blank the #N/A and then the chart will fill the gap where I have blanks.

Thanks in advance!

[REKLP]

Hi @Maria123, did you find a solution to your issue?  I'm having the same problem, I need a dynamic measure.

I'm trying to make the new measure reference the latest value available (In case there's a 2 week gap, or 3 week), but it's not working out so far.

[v-yuezhe-msft]

@Maria123,

Assume that  your original table is as follows. You can create the measures below in your table and then create the expected chart.



Product A = CALCULATE(SUM(Table[margin]),FILTER(Table,Table[Product]="A"))
Measure 3 = CALCULATE(SUM(Table[margin]),FILTER(Table,Table[Product]="B"))
Product B = IF(ISBLANK([Measure 3]),0.3,[Measure 3])



Regards,
Lydia

[Maria123]

Hi!

Thanks for the help! However, the data is refresh each sales week and therefore I don't want to have a fixed amount in my calculation. So I would like to have a dynamic way where it automatically fill the blanks so I can have a line chart for Product B.

Maria

[v-yuezhe-msft]

@Maria123,

Power BI doesn't provide the  “Connect data points with the line”  option like Excel. The workaround to get a continuous line is to create a measure as described in my first reply, for the fixed amount 0.3, you can replace it with another measure which calculates dynamic value.

Regards,
Lydia