2 Replies Latest reply on Nov 18, 2016 12:16 PM by Mike Heenan

# Help with Multiple Average Lines

I have data in which I need to show, on the same worksheet:

1) The sales for a single group by year (listed as category in attached)

2) The average of all sales for the whole group by year

3) The average for top 3 sales by year

I'm able to do 1) and 2) in the same sheet (see Sheet 1) but can't figure out how to combine 3) into there (see sheet 2). I'm not sure how to include rank to create the top 3 but not have it affect the other lines. I'm not sure if there is a way to add if statements into a fixed LOD calculation (ie. fix on year and rank=1).

In my actual data set, there are more variables in which I want the top 3 average to be contingent on. For example, it may be that I want to compare the top 3 just of Type 1 or Type 2.

Any help is appreciated.

Thanks!

• ###### 1. Re: Help with Multiple Average Lines

Mike

You can use parameters to define with whatever dimension you want to compare, and create a calculation like

case[Select Comparator]

when "1" then {FIXED [Year]:AVG( if Rank <= [Compare with Top X ] then  Sales  end )}

when "2" then {FIXED [Year]:AVG( if [Type] = [Compare with Type] then  Sales  end )}

when "3" then {FIXED [Year]:AVG( if Rank <= [Compare with Top X ] and [Type] = [Compare with Type] then  Sales  end )}

when "4" then {FIXED [Year]:AVG( if[Category] = [Compare with Category] then  Sales  end )}

end

see in the attached,

Michel

1 of 1 people found this helpful
• ###### 2. Re: Help with Multiple Average Lines

That's perfect, thanks.