comparison visualization between annual average of value vs each Month of that year( Line&Bar Charts

[Saba]

Hi everyone,
Anybody knows which visualization model can help to design a fixed trend line for specific year over each month in that year? for example I waana have a line and Bar chart which the fixed line shows the total average value over 2015 in different range buckets and bars show the value for Month May 2015 in those ranges and I can go over different months by filter or slicers while the 2015 line doesnt change?

[v-huizhn-msft · Accepted solution]

Hi @Saba

Any trend line in visualization model will be affected by the values in data model. It will change automatically when you filter the data using slicer. From what I know, you can create a calculated column(Average) in table, then create a Line and Stacked column chart, add the average line as follows.

First, I use the data below to reproduce you scenario, and create a column using the formal below, which will calculate the average of each year.

Average = CALCULATE(AVERAGE(Table7[Value]),ALLEXCEPT(Table7,Table7[Year]))

Then, create a Line and Stacked column chart, select the data field as Axis, the Value field as Column values, the Average field as the Line values. Create a slicer including data field. You will get the result as the following screenshot.

 

Finally, the line don’t change when you select one month.

If you have any question, please feel free to ask.

Best Regards,
Angelia