SUMIF function

[Kavadera]

Greetings, colleagues. I am having problems with summing up value. I have a module with products dimension. It's really huge and each new line item adds much space, so I really want to avoid them. I need to count average based on product list property. For example by color (it's not the product hierarchy). Adding theColor listas a dimension is nonesense, because each product has only one colour. And theSUM functionworks only when using one of the module dimensions. I couldn't even yet calculate the sum. I tried this formulas, but nothing worked: DataAvg = Data [SUM: Product List.Colour] DataAvg = Data [SUM: Colour List] DataAvg = Data [SUM: ITEM(Product List.Colour)] DataAvg = Data [SUM: ITEM(Colour List)] DataAvg = Data [SELECT: Product List.Colour] DataAvg = Data [SELECT: Colour List] DataAvg = Data [SELECT: ITEM(Product List.Colour)] DataAvg = Data [SELECT: ITEM(Colour List)] Please help and say there is a way to do this simple calculation.

[PrevContributor · Accepted solution]

Maksim, If the colour list was Red, Yellow and Blue, then how about a summary module with three line items, with Product_List as a dimension, and three formulae: IF Product_List.Colour = Colour.Red THEN Source_Module.Data ELSE 0 IF Product_List.Colour = Colour.Yellow THEN Source_Module.Data ELSE 0 IF Product_List.Colour = Colour.Blue THEN Source_Module.Data ELSE 0 where Source_Module.Data is a module with Product_List as a dimension that holds the aggregate of all product values irrespective of product colour in a line item called Data. You can count the number of values with another three line items each with a formula like: IF Product_List.Colour = Colour.Red THEN 1 ELSE 0 and a final three line items to divide colour values line items by count line items, which will need to have the Summary set to Formula.