Calculate with value that are not in the calculated Dimension

[#1]

Hey all,

I am facing the following problem:

I have several costcenters (in the left box) that belong to a costcenter (right), which constitutes something like the overhead of the left-sided costcenters. One can distinguish these two types by a Field called 'CostCenterMainSub'. The leftsided costcenters have the attribute 'Sub' and the other 'Main'.

The Dimensions of the boxes look like this:

for the left box:

=if(CostCenterMainSub = 'Sub', CostCenter, null())

for the right box:

=if(CostCenterMainSub = 'Main', CostCenter, null())

What i would like to do is to create a ratio as described in the illustration above. I would like to take the value of IST from the right box and divide it by the sum of the IST values of the left box.

I already tried to sum up the left handed values within the right box with the following formula:

=RangeSum(sum({<KostenstelleMainSub={Sub}>}Verbrauchsmenge))

-> unfortunately i didn't work.

Any answers are appreciated!

Tanks,

Sebastian

[tresesco]

Try like:

=sum({<KostenstelleMainSub={'Main'}>}Verbrauchsmenge)/sum({<KostenstelleMainSub={'Sub'}>}Verbrauchsmenge)

[#3]

Hi treseco,

That didn't work.

This part of the formula works:

sum({<KostenstelleMainSub={'Main'}>}Verbrauchsmenge)

this one returns a '0':

sum({<KostenstelleMainSub={'Sub'}>}Verbrauchsmenge)

-> I think the reason is that i defined in the calculated dimension that only 'Main-Values' are considered?

[tresesco · Accepted solution]

use TOTAL  like:

=sum({<KostenstelleMainSub={'Main'}>}Verbrauchsmenge)/sum( TOTAL {<KostenstelleMainSub={'Sub'}>}Verbrauchsmenge)

[#5]

Hey treseco,

TOTAL was the right hint, thank you very much! Is TOTAL always referring to the whole dataset ignoring any reduction of data?

[tresesco]

Ignores any reduction(caused by that specific chart's dimension) of data. Though it has some other important uses.