Total without breaking down by nested values

[Ludijak]

Hello everyone,

I have an issue which doesn't sound complicated but I can't seem to find any solution for days now. I hope I it just me :).

So, I have columns A and A Total that are measures where A Total is just sum of all values of A and is same for each row. Problem arises when I have nested rows with some dimension (lets say B) where it doesn't return same value in A Total as other rows, but it breaks it down according to dimension B. What I want is same value in each row regardless of nested values. I'll try to illustrate it better with example.

What I have:
Name | A | A Total
  a  | 2 |   6
  b  | 4 |   6
 a B1| 1 |   5
 a B2| 1 |   1
 b B1| 4 |   5
 b B2| 0 |   1


What I want:
Name | A | A Total
  a  | 2 |   6
  b  | 4 |   6
 a B1| 1 |   6
 a B2| 1 |   6
 b B1| 4 |   6
 b B2| 0 |   6

Column A Total is defined as (total([A] within set [X(All)]).

If anyone has any idea I would be grateful.

[MFGF]

Hello everyone,

I have an issue which doesn't sound complicated but I can't seem to find any solution for days now. I hope I it just me :).

So, I have columns A and A Total that are measures where A Total is just sum of all values of A and is same for each row. Problem arises when I have nested rows with some dimension (lets say B) where it doesn't return same value in A Total as other rows, but it breaks it down according to dimension B. What I want is same value in each row regardless of nested values. I'll try to illustrate it better with example.

What I have:
Name | A | A Total
  a  | 2 |   6
  b  | 4 |   6
 a B1| 1 |   5
 a B2| 1 |   1
 b B1| 4 |   5
 b B2| 0 |   1


What I want:
Name | A | A Total
  a  | 2 |   6
  b  | 4 |   6
 a B1| 1 |   6
 a B2| 1 |   6
 b B1| 4 |   6
 b B2| 0 |   6

Column A Total is defined as (total([A] within set [X(All)]).

If anyone has any idea I would be grateful.

Hi,

Is it possible the context of measure A is being set by the nesting? You could try using a completeTuple() function for A to forcibly drive the context, and see if that makes a difference.

Without knowing how set X(All) is defined, it's difficult to know for sure, though.

Cheers!

MF.

[Ludijak]

Is it possible the context of measure A is being set by the nesting? You could try using a completeTuple() function for A to forcibly drive the context, and see if that makes a difference.

Well measure A is definitely affected by nesting and that is fine, just I don't want total to be affected by nesting. I don't know how would I use completeTuple() here.

Without knowing how set X(All) is defined, it's difficult to know for sure, though.

I'll try to explain better. Let's say X is Product and it has level Group bellow. On group level it has distinction on B which is type of units (pcs, kg,...).

Something like this:
 Product with nested Group with units
 Product with nested B (Total)

Idea is that each Group has Total row per Units (B).

Now on this I want to have column with total of measure without it differentiating regarding units, pcs + kg +... as total number.

I don't know if it is any clearer now, but I'll try to further explain if needed.

[MFGF]

Well measure A is definitely affected by nesting and that is fine, just I don't want total to be affected by nesting. I don't know how would I use completeTuple() here.
I'll try to explain better. Let's say X is Product and it has level Group bellow. On group level it has distinction on B which is type of units (pcs, kg,...).

Something like this:
 Product with nested Group with units
 Product with nested B (Total)

Idea is that each Group has Total row per Units (B).

Now on this I want to have column with total of measure without it differentiating regarding units, pcs + kg +... as total number.

I don't know if it is any clearer now, but I'll try to further explain if needed.

Hi,

Sorry I wasn't more clear in my previous post. I'm guessing the context is being applied to both the measure and the calculation for the total when nesting. The calculation is simply summing Measure A across your defined set of members (which actually looks like the root member of the Products hierarchy)?

I can think of three of solutions here that might work for you.

1. Change the calculation to be simply a completeTuple of just your measure (so you get the intersection of the measure value with the default member of every dimension). So your calculation would become completeTuple([A])

2. Change the calculation to sum across the set of members in the Product level (rather than the root member of the hierarchy). So your calculation would become total([A] within set [Your Product Level])

3. Change the calculation to include a completeTuple for measure A - which forces the context. You will need to get the intersection of the measure and the current Product hierarchy member to get the correct result, so you will need both a completeTuple and a currentMember function for this. Your calculation would become total(completeTuple(currentMember([Your Products Hierarchy]),[A]) within set [X(All)]).

All three of these approaches appear to work for me when I tried to simulate your issue.

Just for clarity, when I refer to [Your Product Level] above, you should expand your Product hierarchy in the source pane and drag in to your expression the level from your hierarchy that has your Products defined on it. When I refer to [Your Products Hierarchy] above, you should drag in to your expression the hierarchy object from the source pane.

Let us know if either/both of these work for you.

Cheers!

MF.