Create an average line in a bar chart of average values

[ElsaLi]

Hi there,

I wanted to add an average line in my bar chart that is displaying average value of each catagory. However the average line calculates the average of each category average, rather than showing the total average. 

This is for the email marketing analysis. The data I have are open rates of each email, and email types.

I want to make a bar chart that displays average open rate of each type, with an average line running through showing the average open rate of all emails.

Anyone has a solution to this?

Thanks!

[v-juanli-msft · Accepted solution]

Hi @ElsaLi 

Does this achieve your requirement?

If not, please show more details, like sample data, expected output.

Create measures

open rates =
CALCULATE (
    COUNT ( 'Table'[id] ),
    FILTER ( 'Table', 'Table'[status] = "open" )
)
    / CALCULATE ( COUNT ( 'Table'[id] ), ALLEXCEPT ( 'Table', 'Table'[type] ) )

average per type = AVERAGEX(ALLEXCEPT('Table','Table'[type]),[open rates])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

If you need a straight line across all data. try

AVgSales = CALCULATE(AVERAGE(Sales[Sales]),ALLSELECTED())

[ElsaLi]

---

@amitchandak wrote:

If you need a straight line across all data. try

AVgSales = CALCULATE(AVERAGE(Sales[Sales]),ALLSELECTED())

---

Thank you. I tried this way. This is a good way to add a line. But it doesn't look as good as the average line in Analytics. Any suggestions?

[amitchandak]

Can you give an example of what is needed with data?

Or you need a weighted Average like

Over All Avg = CALCULATE(DIVIDE(sum(Sales[Sales]),count(Sales[item_id])),ALLSELECTED())

[ElsaLi]

Thank you. I tried this way. This is a good way to add a line. But it doesn't look as good as the average line in Analytics. Any suggestions?