cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Saba Frequent Visitor
Frequent Visitor

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: comparison visualization between annual average of value vs each Month of that year( Line&Ba

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]))


1.png

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.

2.png  3.png

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


4.png

 


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

Best Regards,
Angelia

1 REPLY 1
v-huizhn-msft Super Contributor
Super Contributor

Re: comparison visualization between annual average of value vs each Month of that year( Line&Ba

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]))


1.png

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.

2.png  3.png

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


4.png

 


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

Best Regards,
Angelia