Forum Discussion
Multi-Year Line and Clustered Column Chart with Trend Line
Hello all,
My boss's boss is asking me to make this chart from excel in power bi:
However, I can only get power bi to show me trend lines (the dashed ones on the chart) when I use year or the full date as the x-axis. aka it will not show the trend line when using month/year as the x-axis. Googling this gives me old versions of power bi or only one year trend lines, and he wants up to 5 years, so any help would be appreciated!
I have thought about calculating the trend line myself however there isn't a place to add in a line (only error bars) when I use the month/year as my x-axis.
I have a date table that lets me choose date, year, month, ect. Here is a sample set of the data. Orange is a count from one table, green and blue are measures.
| Orange | Green | Blue Line | Month | Year |
| 170 | 83.70 | 2.03 | Nov | 2020 |
| 182 | 84.25 | 2.16 | Dec | 2020 |
| 178 | 85.60 | 2.08 | Jan | 2021 |
| 170 | 77.13 | 2.20 | Feb | 2021 |
| 207 | 93.34 | 2.22 | Mar | 2021 |
| 214 | 87.52 | 2.45 | Apr | 2021 |
| 133 | 87.92 | 1.51 | May | 2021 |
| 158 | 88.90 | 1.78 | Jun | 2021 |
| 121 | 86.70 | 1.40 | Jul | 2021 |
| 91 | 85.93 | 1.06 | Aug | 2021 |
| 80 | 85.71 | 0.93 | Sep | 2021 |
| 112 | 91.80 | 1.22 | Oct | 2021 |
| 112 | 88.15 | 1.27 | Nov | 2021 |
| 87 | 85.12 | 1.02 | Dec | 2021 |
| 81 | 88.90 | 0.91 | Jan | 2022 |
| 97 | 88.03 | 1.10 | Feb | 2022 |
Honestly I think he would be okay with the trend lines on a seperate chart if it at least could still show it on a monthly axis. so any suggestions like that would be great too!
1 Reply
- amitchandakSuper User
AbigailCole , Based on what I get for the above Line clustered bar visual you need Year and Month Axis with concatenate label-off, sorted on axis.
keep the measures in y-axis and line as per need. That is best I can think of in standard power bi visuals