Forum Discussion
Continuous line bar chart
I understand that it is quite hard to answer with the little information provided. I will try to make it a little more clear.
Let's say I have a company and I sell bicycles. Another company is a customer of yours and buys bikes to sell them at their store. They tell you that they will buy 5 bikes per month (to get some discount for example). I want to see with a barchart and my data if they are following their promise. The data I have is sale per invoice.
So this is the table:
| yearmonth | bike sales | monthly prognosis |
| 2019/01 | 3 | 5 |
| 2019/01 | 1 | 5 |
| 2019/01 | 2 | 5 |
| 2019/01 | 1 | 5 |
| 2019/02 | 1 | 5 |
| 2019/02 | 2 | 5 |
| 2019/02 | 1 | 5 |
| 2019/03 | 3 | 5 |
| 2019/03 | 1 | 5 |
| 2019/05 | 2 | 5 |
| 2019/05 | 2 | 5 |
| 2019/05 | 3 | 5 |
| 2019/05 | 1 | 5 |
| 2019/06 | 1 | 5 |
| 2019/06 | 2 | 5 |
| 2019/09 | 4 | 5 |
| 2019/09 | 2 | 5 |
| 2019/09 | 1 | 5 |
| 2019/09 | 1 | 5 |
| 2019/10 | 1 | 5 |
| 2019/10 | 2 | 5 |
| 2019/10 | 4 | 5 |
| 2019/11 | 2 | 5 |
| 2019/11 | 1 | 5 |
So now my barchart has a bar per month and a line at 5 bike sales. Some bars are above the line and some beneath the line. However, because there are missing rows, PowerBI does not know the prognosis value for that month so there is a gap between the line between months (see graphs in first post). I know that a solution is to add custom rows for missing months with 0 or 0,01 bike sales and 5 as monthly prognosis, but as I got a lot of companies and also a lot of missing months I do not see that as a good solution. To add up to that, I could not find the function to add custom rows to an existing table.
Could you provide me another solution please? Thanks for your first answer but I do not think that is what I meant, maybe with this it makes it more clear.
Thanks in advance!
Thijs
hi Anonymous
For your case, you could use these two ways to get it:
1. add "+0" in the formula of the measure which is in Values of visual.
2. adjust the formula as below:
Hi Lin,
I don't think this will help because there are no blanks. The rows are missing in the table. So there is no row zero can be added or a row that can be changed to 0,01.
Thanks for the answer though
hi Anonymous
If so, just create a dim date table and create a relationship with fact table, now use the same way as above.
By the way, use date field from dim date table in a visual.
If you still have the problem, please share your sample pbix file for us have a test.
Regards,
Lin