Forum Discussion
Fill gaps in Line chart
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!
4 Replies
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,
LydiaHi!
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
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
