Get Line series in Line and Clustered Column Chart

[Anonymous]

Hi!

I have a Line and Clustered Column Chart where I show Total Units Sold (column values) and Avg Sale (line values). I have a year series to allow comparison from 2013 to 2017-forward.

To be able to get my different series, I have a calculated column for each year. The thing is, I do not want to have to come back to my report on January 1st 2018 (and 2019, and 2020...) to create a new column. 

For the columns, it is easy as I simply need to drag the "year" field into Column Series.

Any idea on how can I get a Line Series for my report?

Thanks!

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[Anonymous]

Hi @v-jiascu-msft

Thank you for your answer. The goal of my request was to avoid to have to write custom dimensions for each year, as I do not want to have to come back to modify the file every year to add the new dimensions.

For the Total Sales, it is easy as I just have to have [Total Sales] in the Column values field, and [Year] in the Column series field. I wanted to know if I could get something similar for the [Average Sale] in the Line values field.

I guess this is an enhancement idea. Thanks!

[v-jiascu-msft]

Hi @Anonymous,

Why not using a measure? If you have separate tables for every year, you can try a measure like this:

2018 Avg Sales = average('Sales2018'[Amount])

If your data is in one big table, you can try a measure like this:

2018 Avg Sales =
CALCULATE ( AVERAGE ( 'Sales'[Amount] ), YEAR ( 'Calendar'[date] ) = 2018 )

Best Regards!

Dale