Calculate Average Dimensions

[#1]

I have a table as below

Date	Product	%low sales
01/01/01	abc	95
01/02/01	abc	97
01/03/01	abc	98
01/04/01	bcd	85
01/01/01	bcd	80
01/02/01	bcd	65
01/03/01	bcd	70

I want to create a chart that will take an avg of low sales and categories them as high performance and low performance based on avg % low sales and give me a product count

So, eg. if avg(%low sales) >95, low performance, high performance)

I cannot seem to get avg calculation working in the Dimension to create these categories to classify my product as low or  high... Please help.

[TKendrick20]

In your Table you should have two Dimensions and two Expressions:

Dimensions (in this order):

Product

Date

Expressions (in this order):

AVG([%low sales])

if(AVG([%low sales]) > 95,'High Performance','Low Performance')

Hope this helps.

[Gysbert_Wassenaar]

You can use this expression: if(avg(total <Product> [%low sales])>95,'low performance','high performance')

See attached example.

[d_pranskus]

Hi

You need to specify the dimension to calculate averages. Please look at the first chart in example attached.

If you just want to categorize already existing %low sales, then look at the second chart. I used DUAL to have custom sort order for performance.

Hope this helps

Cheers

Darius

[#5]

Hi Tim,
  This was working but I need it the other way round, I need a chart that shows, High and low as dimension and a count of products that fall under each category

[#6]

Hi Darius, this might work but not in the current form. you see I have a %low for all days in the past 30 days. for 1000's of products. I want to get an average for the month for each product and them classify the products as low performance or high.
In the table, I do not need date. When I remove the date from Dimensions the numbers are not right

[TKendrick20]

Hmm the closest thing I could get was to have:

Dimensions (in this order):

Product

Date

Expressions:

Label: High Performance: If(AVG(total <Product> [%low sales])>95,Count(DISTINCT Product))

Label: Low Performance: If(AVG(total <Product> [%low sales])<=95,Count(DISTINCT Product))

hope this helps point you in the right direction.

[d_pranskus]

Then try to use the following expression for your dimension

=IF(AGGR(AVG([%low sales]), [Product]) > 95, 'High performance', 'Low performance')

Cheers

Darius