Calculated measure on calulated categories 1

[spicysudhi]

hi

The transformer model has three measures
1. Value : Source from IQD
2. Cost : Source from IQD
3. Margin % : Calculated as (Value-Cost)/Value

The rollup type for the margin is set as default (After Rollup).

Below is how my cube looks when opened

Part no            Value                  Cost          Margin%
Part A             10                       5              50%
Part B             20                      10             50%

The calculation is absolutelly working fine here. however, what our use doing is, they are defining a calculated row, by adding PartA and PartB. Then the output comes like this

Part no            Value                  Cost          Margin%
Part Total          30                       15              100%

If you look in the above example, the Margin is not based on the original calulation, it is the SUM of actuals (50% + 50%)

Anyway to get around this problem instead of asking the user to create a calculated margin column?

regards,
Sudhi

 

[GJParker]

The only way around this is to create the calculated category in the model as a special category.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[XCog]

The user can be told to use "Rollup" option when creating the calculation instead of SUM(which is the default). This will take care of the percentage rollup also.

 

[GJParker]

How long has that option been there ?

I don't use Powerplay much these days and I'm sure that never used to be there, nice one xcog.



Gary Parker
MIS Data Analyst
Manchester, England

 

[spicysudhi]

XCog,

the problem in my case is the Margin% is defined a measure in cube. I contacted cognos on this and they said there is no solution. Only way is to derive a new calculation (for Margin) in the Powerplay report for the calculated categories and this works.

regards,
sudhi