cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Reference line for Clustered Column chart

Hi All,

 

I am trying to add a reference line after year 2020 in below clustered column chart to differentiate Past Present and Future values.

 

Y axis contains $ vale in year and X contains years.

 

I am not able to find a way to do this. Please help!

 

 

image001.png

8 REPLIES 8
v-lili6-msft
Community Support
Community Support

hi   @Anonymous 

You could try this way as below:

Create a measure as this logic

Avg after 2020 = IF(SELECTEDVALUE('Table'[Year])>=2020,CALCULATE(AVERAGE('Table'[Net Flow]),FILTER(ALLSELECTED('Table'),'Table'[Year]>=2020)))

In line and clustered chart visualizations, drag this measure in line values

4.JPG

 

and here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
moltra
Helper IV
Helper IV

Have you tried a line and Stacked Chart or line and clustered chart visualizations?

Anonymous
Not applicable

Yes, I have tried line and stacked Chart but couldn't get the reference line after coloumn 2020.

Can you post a representative table of your data?

Anonymous
Not applicable

Thank you for the reply.

 

can you post it as a table, so I can copy the data.

Anonymous
Not applicable

YearNet Flow
2005-111
20062.89
20074.7
2008-33
2009125
2010270
2011410
2012422
2013544
2014455
2015377
2016154
2017-45
2018-136
2019395
2020-527
2021-777
2022-980
2023-1100
2024-1336
2025-1570
2026-1780
2027-1800
2028-2103
2029-2388
2030-2400
2031-2970
2032-3100
2033-3300
2034-3590
2035-3650
2036-3900
2037-4120
2038-4300
2039-5160
2040-5371
2041-5365
2042-5464
2043-6310
2044-6,564
2045-6,856
2046-6900
2047-7150
2048-7330
2049-7500
2050-7680

I figured out one way of doing it, it is more a work around than anything, but you can create your normal clustered chart, and then create another clustered chart and have one data point in the table date: 2020 and net flow: -8000.  you can then place the original chart in the back and place the 2nd chart on tom and then move to the correct position on the chart.  You then shrink the chart as small as you can in width and place it over the 2020 date.  

 

you also would need to turn off all the titles, and informtion for the 2nd table so they do not show up.  If you turn off the responsive you can make the line thinner.

 

moltra_0-1594243457182.png

 

I am going to continue working on a more elegant solution, but if you like this answer then please accept this as the solution.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors