Forum Discussion

jayasurya_prud's avatar
jayasurya_prud
Icon for Advocate III rankAdvocate III
3 years ago
Solved

Line and Clustered column chart y axis columns

HI, 

 

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

 

my data is  of two tables

 

projected_1

Year_1rates_%
201155%
201160%
201165%
201470%
201575%
201580%
201585%
201890%
201995%
2020100%
2020105%
2022110%
2023115%
2023120%
2023125%
2026130%
2027135%
2027140%
2027145%

 

 

projected_2

 

Year_2rates_%
201435%
201440%
201445%
201450%
201855%
201960%
202065%
202070%
202075%
202080%
202485%
202590%
202595%
2025100%

 

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?

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    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.

1 Reply

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.