Static reference line for Average calculation

[#1]

Hi,

I am trying to add a reference line in my chart to show average of sales. However, it varies based on my selection. Is it possible to make it static? I am using the formula avg(sales) in the reference line expression on Presentation tab.

Thanks,

[rwunderlich]

Change your reference line expression to ignore selections:

avg({1}sales)

-Rob

[#3]

Thanks Bob! This was very helpful. However, this is working only partially. For example: In my chart, I have drill down group for my dimension (Categories and Sub-categories) on x-axis with Category values A,B,C,D - initially the average value shown by reference line is correct. But when I select any of the category (drills down to all the sub-categories within that particular category), the reference line value changes to point the average value for that category. Selecting any of the sub-categories NOW does NOT change the average value. But I want the initial average value to remain constant even if I pick any category. Is this possible?

Thanks,

[simondachstr · Accepted solution]

I'm not quite sure I get what you are trying to achieve but try the following expressions:

=avg(total {1} sales)

=avg(all {1} sales)

=$(=avg({1} sales))

='$(=avg({1} sales))'

This is a little bit of a try & error approach and I apologize for that but I hope one of the expressions will do.

[sunny_talwar]

PFA an example qvw file, is this what you are trying to achieve?

Best,

S

[rwunderlich]

I'm not understanding what you are asking for. could you explain it in terms of:

Clear State: avg of Categories A, B, C.

Select Cat B. Avg of Subcategories B1, B2, B3? Or something else?

-Rob

[#7]

Thanks Martin!    =avg(total {1} sales) worked!

Thanks,