Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Continuous line bar chart

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

Need Help

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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:

      yearmonthbike salesmonthly prognosis
      2019/0135
      2019/0115
      2019/0125
      2019/0115
      2019/0215
      2019/0225
      2019/0215
      2019/0335
      2019/031 5
      2019/0525
      2019/0525
      2019/0535
      2019/0515
      2019/0615
      2019/0625
      2019/0945
      2019/0925
      2019/0915
      2019/0915
      2019/1015
      2019/1025
      2019/1045
      2019/1125
      2019/1115

       

      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's avatar
        v-lili6-msft
        Community Support
        6 years ago

        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.

        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
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        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).