SUMIF function

Highlighted
Super Contributor

SUMIF function

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 the Color list as a dimension is nonesense, because each product has only one colour. And the SUM function works 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.

1 REPLY 1
Highlighted
Previous Contributor

RE: SUMIF function

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.