1. TARGIT Community
  2. Community
  3. Frontend, Designer and Consumer
Knowledge Base

Referencing the total of a measure with a hidden unknown member in the cube

Rasmus Remmer Bielidt ·

How can I reference the grand total of a measure when I have a dimension with an unknown member that is hidden on the dimension itself in the SSAS cube?

In the below, I would like the value to be 3468433 - but it returns 124631 only. I am not referencing the wrong measure and I tried adding the a switch for all (hidden and visible) - but it is as if TargIT does not include the values for the unknown measure which was hidden on the dimension level - but it does include it in the Total sum as can be seen from the cross-tab.

 

If I make the same calculation using a cube where the only difference being the Unknown dimension member set to visible on the dimension itself it references the value correctly.

0

Comments

3 comments
Sort by DateVotes
  • Ole Dyring

    Hi Rasmus,

    What do you get if you try this syntax instead:

    sum(d1, d1(l0), m2)

    ?

    BR / Ole

    0
  • Rasmus Remmer Bielidt

    Hi Ole,

    That works perfectly, thank you very much.

    Would you mind explaining what the d1 and d1(l0) actually mean in this context? I usually try a lot of different formulas before I find the one I need, even if I have been certified TCP 15 years ago :D

    0
  • Ole Dyring

    Hi Rasmus,

    In your crosstab, on the horizontal axis, you have two dimensions or two levels of a hierarchy: Customer waste and Reporting group.

    • Customer waste is level 1 (l1). The subtotals for 'No' and 'Yes' are on level 1.
    • Reporting group is level 2 (l2)

    However, there is always a level above level 1. You can always use level 0 (l0) to refer to the Grand Total of a dimension.

    BR / Ole

    0
Please sign in to leave a comment.