Avg of a bar chart

[alec1982]

Hi guys,

I have a bar chart with the following expression:

=Sum([Rental Cost])/(Sum([HC])/Count(DISTINCT Year))

Now i have a request to show a line across the chart for the avg of the same expression.

Anybody knows how to do that.

The chart is a combo chart and i tried to create another expression but didn't know what the expression for this line should be.

[alec1982]

Hi,

Thank you for your help.

Is there anyway I can do that in the expression tab as another expression so It appears in the Legand.

Thxs,

[#3]

Sorry I thought I misunderstood your question. Yes, if you enter it as another expression as a combo chart, it will show up in the legend as usual. You can also put it on a secondary axis if needed, as an average versus a sum may have very different axis requirements. I will post an example for you.

[#4]

Here you go.

[alec1982]

Hi,

That's excactly what I want but since I have more complecated Expression in the first expression

=Sum([Rental Cost])/(Sum([HC])/Count(DISTINCT Year))

What should be the expression in the second one (the Avg)

[#6]

Your expression looks like it is already an average; do you have a sample file? Your formula is being read as: (Total Rental Cost/Total HC)/Number of Years. I am not sure if this would be accurate or not, but try:

=Avg([Rental Cost])/(Avg([HC])/Count(DISTINCT Year))

[alec1982]

I tried to do the following but didn't work

=Sum({1}[Rental Cost])/Sum({1}HC)*Count(Distinct Year)

If i put this expression in a text box it returns the right nr.

When I add it to the chart it get effected by the dimenssion.

Is there anyway i can fix that

Thxs

[#8]

What is the end goal of what you are trying to find? For example, average Rental Cost per year? What are the X and Y axes of your combo chart?

[alec1982]

Hi,

The X is City

and the Y is =Sum([Rental Cost])/(Sum([HC])/Count(DISTINCT Year)) which is Rental Cost Per Person

The Avg line is to show the average for all cities. Like one straight line that goes across the chart to show the AVG of all values.

Example:

X          Y

City1     10

City2     20   

City3     15

Avg = 15 showed as a straight line across the chart.

Thxs

[#10]

Are you sure that expression is giving you the Rental Cost Per Person? It looks like Rental Cost per HC per Year. Maybe (Sum(Rental Cost)+Sum(OtherApplicableCosts))/Sum(Persons) or something. Then the average would be the same expression.