Average Line By Month

[chhavi376]

Hi All,

I Have a Geography Drill down (Area, Region and Territory)

My requirement is to create a line chart by month and geography drill down, with an average line. (If we are on Area, we get a  National Average, if we select one Area, we will get our chart by month and the regions corresponding the selected area, and we would want to display an average line for the selected area.

Case 1: Nothing selected 

Case 2: Area = East is selected, we get a line with East Average

Can you please help me with the same?

Thanks in advance.

[Lisa_P]

You will first need to make your MonthYear into a field in your load script.

Then you can use nested if statement in your average reference line calculation combined with Aggr function. 

eg

If(GetSelectedCount(Area)>0,    
Avg(Aggr(Sum(Value), MonthYear, Region)),
Avg(Aggr(Sum(Value), MonthYear, Area)))

//  If any areas are selected use the Average Value per MonthYear and Region, else per MonthYear and Area