adding a annual average line to a plot that contains month in the filter

[CL7777]

Hi all,

I consider myself an intermediate dax user, but cannot sometimes figure out things that seem simple. here is my latest quandry. I have a table (sales table) that has the transaction date, the part number, the location, and the quantity. I created a simple clustered column chart that shows the quantity sold. the x axis of this chart is the date field (month and year). I want to add a line to this chart that shows the annual average of quantity sold as a straight horizontal line across the graph. the problem I am having is that the average quantity sold value keeps getting filtered by both month and year (and not just year), I have tried to create a measure with the following code:

average 2017 = CALCULATE(

CALCULATE(SUM([Qty]), FILTER('_Consolidated Part Usage Monthly ss', [year] = 2017)) , ALLEXCEPT('_Consolidated Part Usage Monthly ss','_Consolidated Part Usage Monthly ss'[Year]))

I have also tried using allselected instead of all except. These are the result that I get when I do those two commands. (see plots below). I want to have a straight line for 2017 because there should be one value that is the average quantity for that year. but the line is varying by month within the year. 

I also have slicers on the page the filter for part number and store location so the constant average value (the straight line) should change for the slicers selected.

Im stumped. please help!

[parry2k]

@CL7777 are you looking for something like this, shows average line per year

[CL7777]

yes, that is what I am looking for, but the line itself (the red one in your plot) does not show the average of the data below it. Its too hight. I want the line to be the average of the column heights (the average quantity over the year)

[parry2k]

@CL7777 I think you mean this.

[CL7777]

no, the red line would be the average value of the quantity over the year. So, the line would have the same amount of data (blue column data) above it as below it. The average value of the blue columns over each year is the line I want. The new line you put in looks like it is getting the max value

[parry2k]

@CL7777 your request is not clear, it it avg, just becuase my dataset is like this, it seems that it max.

do you want avg line for each year that is what it is doing, as you can see, when year changes, the avg line changes

or  do you want avg line for all the year, just one straight line. please let me know what you want, you know your data and your problem better than I do, better you explain, better solution you will get.

[CL7777]

something like this...

[parry2k]

@CL7777 it is what exactly what  my output is, my dataset has very similar number and thats' why average looks different, solution is attached.

Look at page 2 and related tables, there is other stuff in the file, just ignore that.

Would appreciate Kudos 🙂 if my solution helped.

[CL7777]

Thanks so much for working on this, however it is still not working for me. I have implemented your suggestion and I still do not get a straight line (because I have other filters). I am trying to attach my pbix file. Can you look at it and let me know why your solution doesnt work in my data? thanks so much.. 

https://www.dropbox.com/s/jmwftk9zqa3lbjw/Part%20History.pbix?dl=0

[parry2k · Accepted solution]

@CL7777 the problem with your meaure was to use year from Calendar table instead of data table, once it was change, everything worked as expected. 

Would appreciate Kudos 🙂 if my solution helped.

[CL7777]

I want the average line for each year, so the line would be constant for 12 months and then change.