Line and Clustered column chart y axis columns

[jayasurya_prud]

HI, 

I am trying to build a line and clustered column chart. 

my data is  of two tables

projected_1

Year_1	rates_%
2011	55%
2011	60%
2011	65%
2014	70%
2015	75%
2015	80%
2015	85%
2018	90%
2019	95%
2020	100%
2020	105%
2022	110%
2023	115%
2023	120%
2023	125%
2026	130%
2027	135%
2027	140%
2027	145%

projected_2

Year_2	rates_%
2014	35%
2014	40%
2014	45%
2014	50%
2018	55%
2019	60%
2020	65%
2020	70%
2020	75%
2020	80%
2024	85%
2025	90%
2025	95%
2025	100%

The problem is, in the  line and clustered column chart,

my x axis is Projected 1 - Year_1 column

my column y axis are avg(Projected_1[Rate_%]) & avg(Projected_2[Rate_%])

but when I plot it, 

I could see that the plot are being generated for even projected_2 records where the years doesn't listed. 

how deal with this?

[v-yadongf-msft · Accepted solution]

Hi @jayasurya_prud ,

Please combine two tables into a new table:

Table = UNION(
    SELECTCOLUMNS('projected_1',"Year",[Year_1],"rates_%",[rates_%],"Type","projected_1"),
    SELECTCOLUMNS('projected_2',"Year",[Year_2],"rates_%",[rates_%],"Type","projected_2")
)

Create a measure to calculate average of rates:

Average_rates = AVERAGE('Table'[rates_%])

I think this is the result you want:

Best regards,

Yadong Fang

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