Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Growth Line Bar Chart (Linear)

Hey!

 

I am looking for a way to add a linear "target line" to my line/bar chart.

What I am looking  for is to use januari Total Sales as a base line and to add a growth line based on a % (determined by me) increase.

 

Lets say we had 100 sales in januari (2022) and for a target we've established we want to grow 10% every month

Expected results:

Jan: 100
Feb: 110
Mar: 121
Apr: 133

Etc.

 

For the bars in the chart I put the actual Sales per month, wheras the line should be above mentioned "targets". 

The report is based on a Tabular connection, which makes it slightly more difficult to add custom data / columns, so I haven't figured out an easy way to do this.

 

Any tips or tricks?

Would love to hear it! 🙂

 

Kind regards,

Daniël

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    According to your description, you want to get the target value based on the 10% growth every month.

     

    If so, I have created a data sample:

    Please use the following formula to create Target measure:

    Target = 
var _minDate=MINX(ALL('Table'),[Date])
var _minValue=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Date]=_minDate))
var _monthDiff=DATEDIFF(_minDate,MAX('Table'[Date]),MONTH)
return _minValue  * POWER(1.1,_monthDiff)

    And then create a Line and stacked column chart:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    According to your description, you want to get the target value based on the 10% growth every month.

     

    If so, I have created a data sample:

    Please use the following formula to create Target measure:

    Target = 
var _minDate=MINX(ALL('Table'),[Date])
var _minValue=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Date]=_minDate))
var _monthDiff=DATEDIFF(_minDate,MAX('Table'[Date]),MONTH)
return _minValue  * POWER(1.1,_monthDiff)

    And then create a Line and stacked column chart:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for the swift reply, Eyelyn!

       

      Sadly the above mentioned measure returns blank. Could this have to do with how my data structure is set up? (Tabular model)

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    It seems that your Date column is Text type not Date type. Please check.

     

    If the error still exists , I'd suggest you share some dummy data for us to test. Refer to:

    How to Get Your Question Answered Quickly - Microsoft Power BI Community

    How to provide sample data in the Power BI Forum - Microsoft Power BI Community

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.