Adding Group Average Line to Bar Chart

Question

I am trying to create a PivotChart in the form of a bar chart and I would like to add a group average line on the chart.

I have this (sample) table in my PowerPivot Data Model:

Group averages are A: 20, B: 50 and C: 80. My bar chart looks like this.

How would I do this? Do I need to add a measure? If so, how? I am not that great with DAX functions yet.

Answer 1

While I am not sure this is what you are looking for, here is what I think you are asking.

First, you are going to need an additional column. Two ways to get er done.

1. Calculated column

   =CALCULATE ( AVERAGE ( Range[Value] ), FILTER ( ALLSELECTED ( Range ), Range[Group] = EARLIER ( Range[Group] ) ) )

2. Measure

   =CALCULATE ( AVERAGE ( Range[Value] ), FILTER ( ALLSELECTED ( Range ), Range[Group] = MAX( Range[Group] ) ) )

Now edit your pivot table:

Now select your chart type:

For future reference you should look into Power BI. It has features which do these sorts of calculations automatically.

If your looking for something different it would help is you provided the expected outcome.