SUMIF function

Kavadera
Kavadera
in Modeling

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.

Tagged:

Best Answer

  • PrevContributor
    PrevContributor
    Answer ✓
    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.
     

Categories