add an average line on a Line and Clustered Column Chart

[cooke01]

Hi,

I have a simple data set that I need to show an average for as a line over a bar graph.  I have about 8 years of data with only two columns Date and Appointment Type. I have created a basic column visual that lets me see how many of each appointment type we have completed in each day, month etc.

I would like to have a calculation that sits over the top that shows what the average in that time period has been for that particular set of filtered appointment types and time period. So for example if I am looking at the last 12 months I would like to see what the average number of that appointment type has been over that past 8 years for each month to see how we are performing. If I am looking at the quarter again have it automatically change to show the average for the QTR. 

Thanks in advance for the advice. 

[v-shex-msft]

HI @cooke01,

According to your description, you want to create dynamic average line of current selected date range, right?
If this is a case, you can try to use below measure to calculate the dynamic average:

Dynamic Average= IF(ISBLANK(MAX(Table[Date]))=FALSE(),CALCULATE(AVERAGE(Table[Amount]),ALLSELECTED('Table')))

Regards,

Xiaoxin Sheng

[JDLee23]

Hi,

Probably the easiest way to do this is to use the 'Analytics' feature on the bar graph. Underneath the various visualisations, in the same area where you put the data types into a visual, there should be magnifying glass with a line chart within it (select your bar graph first!).

This will then give you options to add various lines, including an average line, which will automatically filter to show the average of the data in the bar chart, as filtered by any slicers on the data.

Hope this helps!