Levels with multiple dimensions on the same axis

  • Add another crosstab looking at Revenue per Salesperson,  by Product Hierarchy(Product Group) and by Customer(Territory).

Now make sure that all 3 dimensions are on the same (vertical) axis as shown here:


This creates a special situation in relation to the level parameter. You can actually reference the level of each dimension in one sentence.

This example references the totals of the Salespersons:

Salespersons totals: sum(d-1, 0(l(1, 0, 0)), m1)


l(1, 0, 0) means level 1 on the Salesperson dimension and level 0 on Product and Customer Country (level 0 being the “all” level).

So in short – totals for Products and Customer Country but still within each Salesperson.


To reference the Product subtotals the syntax would be:

Product Subtotals = sum(d-1, 0(l(1, 1, 0)),m1)


Totals on the Customer Country level – but within each Salesperson/Product Group.

Named reference in combined heirarchy

In the above example, in case you needed to refer to a specific cell, using the “@” reference method, the syntax should be:

                      sum(d1, @”[Alvaro Bennett]”;”[T-SHIRTS]”;”[Europe]”, m1)

Note: Even if one of these levels is hidden, e.g., the Product Group level, it should still be included in the named reference syntax.

Was this article helpful?
0 out of 0 found this helpful


  • Hi,

    Is this possible to do with the average function, rather than the sum? 

    I tried using the following: avg(d-1, 0(l(1,0,0)), m3)

    But it returns the sum rather than the average. 

  • Hi Lara,

    Your syntax, avg(d-1, 0(l(1,0,0)), m3), indicates that you are calculating the average of the current row - i.e. the average of a single cell is identical to the sum of a single cell.

    If your average calculation should make sense, it should probably be one of either:

    • avg(d-1, all(l(1,0,0)), m3), or
    • avg(all, 0(l(1,0,0)), m3)

    BR / Ole

  • In this example, would it be possible to calculate the subtotal of Jeans across Salespersons?

    Something like sum(d-1, 0(l(0, 1, 0)),m1)? This does not work out for me...

    BR Michala

  • Hi Michala,

    You can do that, but you will need to work with 'Multidimensional subtotals'.

    See this article: https://community.targit.com/hc/en-us/articles/360017917778-Multidimensional-Subtotals 

    BR / Ole

  • Thanks, Ole.

    I can't crack the code. My table contains 6 separate dimensions as seen below. I want to calculate the average "Sales price EUR" per MPN (1, 2, 3,4 and 5), and I want to show it in the column "Avg Sales price" for each line.

    How do I do that?

    BR Michala

  • Hi Michala,

    I am sorry, but I don't think it will work for you. The Multidimensional subtotals are only good for giving you exactly that: The *total* of every dimension member in a mixed hierarchy, independent of its current level.

    To transfer it into something useful in a table where it should show the correct multidimensional sum (or the average, for that sake) for each corresponding Product Group (or corresponding MPN in your case) would be impossible, I am afraid.

    BR / Ole


  • Hi Ole,

    Okay - too bad. Thanks for your time :-)


Please sign in to leave a comment.