6 Replies Latest reply: Jan 29, 2018 3:08 PM by David Forest

# sum of total sales based on values below an average % range

Hi All.

I need a KPI to sum the total sales below 25% of the average sales. The average sales % calculates as follows

avg(sales)/avg(total sales).

for example if my sales average is 100 I need to sum up all the vales below 25

I'm sure it's a aggr function but I can't get it to work

• ###### Re: sum of total sales based on values below an average % range

Would you be able to show what you have by sharing a sample or by showing some images?

• ###### Re: sum of total sales based on values below an average % range

untested but:

sum(if((avg(sales)/avg(ALL Sales)) <0.25, sales, 0))

• ###### Re: sum of total sales based on values below an average % range

Hi David,

why the zero at the end?

• ###### Re: sum of total sales based on values below an average % range

Just the false part of the If, not really necessary if Summing I suppose.

• ###### Re: sum of total sales based on values below an average % range

I answered my own question as follows

sum(aggr(if (Avg(Sales)/Avg(TOTAL Sales) <0.25,Sales ),Sales))

• ###### Re: sum of total sales based on values below an average % range

Aggregating this by sales? Not a great idea... but if it works for you... then feel free to use it