Continuous line bar chart

[ThijsTUE]

Hi,

Hope you are fine during this Corona time.

I was working on a Power BI report and I came across a problem with my bar chart.

I have a bar chart with some data about revenues and their forecast. The forecast value is a line and their revenue is calculated per month. The bar is green when above the line and red when beneath the line. This works fine. However not every month has sales for the certain company so some months have no data.

So the graph looks like this:

As you can see for the months with no data the line value does not show. I understand this because the value for the line is in a column and for companies with no rows for such months with no data their is also no value for the line.

I tried to find a function to add rows myself, where I can set every value to 0 or 0,01 and the line value to the right value and that then for every empty month, but could not find this.

Could someone else provide me a solution to this? It would help me a lot.

Thanks in advance

If I need to add more pictures let me know, however I can not share all because some are business secret.

Do

[Greg_Deckler]

Sounds like you perhaps want to create a summary table using something like SUMMARIZE?

Table =

  ADDCOLUMNS(

    SUMMARIZE('Original Table',[Month],"Sum",SUM([Column])),

   "Final",IF(ISBLANK([Sum]),0,[Sum])

  )

Or maybe you add that if statement to your measure?  It's really, really difficult to say without sample data to recreate. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[ThijsTUE]

Dear @Greg_Deckler, could you please look at my response with the more in detail explanation of my answer and let me know if you still cannot help me, thanks!

[Greg_Deckler]

Create a Date table with all your months in it and relate it to your fact table (sales) based on yearmonth columns. Create a customer table with all your customers in it and their monthly committments. Relate this dimension to your fact table based on customer columns.

Relationships should be:

Date 1 -> * Sales * <- 1 Customers

You can use Customer as a slicer. Use YearMonth from Date table as your X-Axis. Use Committment column from Customers table as line value. Use your sales count from Sales table as your column value. You will have a line from every yearmonth (should).

[ThijsTUE]

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

[v-lili6-msft]

hi @ThijsTUE 

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.

Measure = CALCULATE(SUM('Table'[bike sales])) + 0

2. adjust the formula as below:

Measure 2 = IF(ISBLANK(CALCULATE(SUM('Table'[bike sales])) ), 0.01, CALCULATE(SUM('Table'[bike sales])))

Regards,

Lin

[ThijsTUE]

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

[v-lili6-msft]

hi  @ThijsTUE 

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