Reference line in Bar Chart

[Sharbel]

Hello,

I have the following bar chart:

the x-axis in the chart above  shows date value in the highest hierarch (i.e Years), but it can also drill down to lower hierarchies such as Months and calendar days. 

I am trying to add Reference line in the add-ons section (as shown below) that displays Average visits per Hierarchy, but unfortunately couldn't figure out how to calculate an average that is responsive to the hierarchy shown in the chart.  

For example:

When the hierarch shown in the chart is Years (as displayed in the chart above) , then the reference line should show the value of  (430,292/14) = 30,735 visits

and when we drill down to months in year 2022, then the Reference line should show the value of (34,654/12) =  2,887 visits and so on for calendar day hierarchy.

 any ideas? 

Thanks,

Sharbel

[brunobertels]

Hi 

you cant add a reference line with an average mesure in a drill down dimension. 

when you swip between Years month or Date , you don't make a selection in this dimensions. 

I will advise to create button with variable to swip between your period dimension Date, week , month etc 

Then you will be able to add a reference line depending of the selected dimension in the variable. 

Regards 

[Sohan_Patil]

Hi,

Sample data of 3 years 

after selecting year 2020 then

reference line expression : 

=If(GetSelectedCount(Year)>0, sum(Visits)/count(distinct Month), sum(Visits)/count(distinct Year))

[Sharbel]

Hi,

the above expression seems to work on two layers of hierarchy ( Year, Month) ,

what the expression would be in case i have three layers of hierarchy (Year, Month, Calendar day)?

Sharbel

[Sohan_Patil]

Hi.

I have added one more field (Days) in the script 

Year 2019 and Month Apr is selected

Reference line expression:

=If(GetSelectedCount(Year)>0, if(GetSelectedCount(Month)>0,sum(Visits)/count(distinct Days),sum(Visits)/count(distinct Month)), sum(Visits)/count(distinct Year))

[Sharbel · Accepted solution]

hi @Sohan_Patil 

works perfect

Many thanks