How do I sum across dimensions other than time?

iosman123
iosman123
in Integrations and extensibility

Hello

 

Can't see how this was done in the allocations video. I have a list for products, and a line item for sales. I want to introduce a line item that returns the total sales for all products against every product. 0x80070490 Beyond allocation use, is there a way to introduce a line item that returns the value at the next level up a multi-level hierarchy? - Assume we have a three level product hierarchy: Product>Product Group>Total Products. Such a function would enable me to return the % contribution that each Product contributes to its Product Group, and the contribution that each Product Group contributes to Total Products.

 

thanks

iosman

Tagged:

Answers

  • anirudh
    anirudh

    Hi iosman,

     

    This is certainly possible. Simply create a new line item called Total Sales, without any dimensions and with the formula as Sales.

    Then create a third line item called % of Total Sales with the formula as Sales/Total Sales

     

    image.pngimage.png

     

     

    This works because your products hierarchy has a top level called Total Products

    Please let me know if this helps!

     

    Regards,

    Anirudh

  • bram.kurstjens
    bram.kurstjens

    Hi Iosman,

     

    Unfortunately I have a very complicated answer for your very simple question, but it does work. It will require a few more line items though! If I understand correctly, you want to see the percentage of each product relative to its parent. As in the following screenshot: 

    image.png

     

    Sidenote: I have a rather standard way to determine which level of the hierarchy the cell is refering to. I use this for filtering tables where I want to show a certain level, while I want to use synchronize selection on the dashboard (selecting levels in a view, disables the synchronize selection function), but it can also be useful for these kinds of formulas. In order for this to work, you need to know how many levels the list will have and it will require maintenance if you add another level.

     

    image.png

     

     

    Here are the steps to create this result:

    1. Create a line item "Ratio", set the value to 1 and set the summary to 'MIN'
    2. Create a line item "Lvl 2", set the value to 2 set the dimension to Product Group and set the summary to 'Ratio' with parameters: "Ratio / Ratio"
    3. Create a line item "lvl 3", set the value to 3 and set the summary to 'Ratio' with parameters: "Lvl 2 / Ratio"image.pngYou now have a line item that shows a 1 when it is on total products, a 2 when it is on a product group and a 3 when it is on a product:image.png
    4. Add a Product Group line item with format Product Group which refers to the parent of product PARENT(ITEM(Product))
    5. Add a "Sales product group" line item which does a look up on Sales using the Product Group line item: Sales[LOOKUP: Product group]
    6. Add the last line item "Sales %" with as summary "FORMULA": 
      IF 'Lvl 3' = 1 THEN 1 ELSE IF 'Lvl 3' = 2 THEN Sales / Sales[SELECT: Product Group.All Products] ELSE Sales / Sales product group
      image.png 
    7. The formula checks the level and determines its calculation based on that level so,
      1. If 'lvl 3' = 1 that means the current product dimension is on Total Products, thus 100%.
      2. If 'lvl 3' = 2 that means the current product dimension is on Product Group, thus Sales devided by the total sales.
      3. If 'lvl 3' = 3 that means the current product dimension is on Product, thus Sales devided by the sales of the relative product group.

     

    Hope this is not too complicated!

     

     

    KR,

     

    Bram Kurstjens

Categories