Sum in Hierarchy problem

[jorditorras]

Dear Gurús, 

I'm reading a KPI "Number of Hours" from a table where users fill the number of hours by Plant and, in some cases, they also sometimes fill manually the number of hours by Company (an upper level of Organization hierarchy) where it is the same as the sum of all the Plants. See an example below where Hours in the global Company A is also filled: 

Hierarchy Level		Number of Hours
Level 1	Hours Company A	110
Level 2	Hour Plant 1	30
Level 2	Hour Plant 2	25
Level 2	Hour Plant 3	15
Level 2	Hour Plant 4	40
Level 1	Hours Company B
Level 2	Hour Plant 5	5
Level 2	Hour Plant 6	10
Level 2	Hour Plant 7	10
Level 2	Hour Plant 8	5

The problem is that, when I calculate the number of hours by Company A, the result is wrong as it sums 220 (sum of all Levels 2 + Level 1). But if I calculate the Hours by the next level of hierarchy (Level 2), then the result it correct (110). 

How can I do this calculation by Company A to appear correct? 

Thanks in advance. 

Regards, 

Jordi

[marcus_sommer]

You may exclude this level from the aggregation, maybe with something like:

sum({< { [Hierarchy Level] -= {'Level 1'}>} [Number of Hours])

But I suggest also to consider to exclude these values already by loading the data or even excluding the whole records because such TOTAL's within the data are most often not needed.

- Marcus

[jorditorras]

Hi Marcus, 

"Hierarchy Level" is not a dimension. The dimensions are "Level 1" and "Level 2". They appear both in same column as it's focused as a pivot table expanded. 

Also, if I exclude "Company A" (Level 1) in a Set Analysis, then the result will be 0 as I won't see his result and neither the results of the Plants (Level 2).

Thanks for your suggestion though!

[marcus_sommer]

It's not really clear for me how your data-structure look like. I'm quite sure that I wouldn't load these TOTAL values. If you exclude them there they won't make any trouble anymore.

- Marcus