Static Trend Line for Column Chart

[Anonymous]

Hello Community - 

I have a basic measure that is calculating the weekly average based on a the selected date ranges.

Measure is:  

Wkly Avg = DIVIDE(Total POs, ISO Wk Count, 0 )

Total POs is a basic SUMX measure and ISO week count, just counts the number of weeks within the date range selected.

What I am looking to do, is to show that specific average across the entire column chart and only will move up or down based on data that is sliced.  I would like to see something similar to below with the red dashed line being that average that is calculated based on the above measure.

Thanks in advance for any help!

Ryan

[negi007]

@Anonymous you can use quick measure to create a rolling weekly average and then use this in the line values that should do the job for you. 

To create quick measure, right click on your base column and then select quick masure.

pl. try this and write back if you face any issue. thanks

[negi007]

@Anonymous if you want to have a average trend line in your chart that you can have easily from analytics tab -> average line. This will use your chart data and according to the time period selected will change

in case you wish to achieve anything else, please write back. thanks

[Anonymous]

@negi007 thanks for the response, unfortunately average lines are not available in the analytics pane for the combo charts.  So I am looking to create it as a measure so that I can get the result that you are showing above.

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Is the gif similar to your expected output?

You can create a new date table from your fact table:

Date = values(fact[date])

Then use the new date column as slicer for you to select date range you want. And then you can use the following measure as average lines

Wkly Avg = CALCULATE(DIVIDE(Total POs, ISO Wk Count, 0 ),TREATAS(VALUES('Date'[date]),'fact'[date]))

You can refer to the sample pbix file.

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

Best Regards,

Dedmon Dai