Creating a Percent of Total within a calculated set

[antya76]

Help! I have been working on resolving an issue for the past few days and have given up and asking for help.

I have data that contains 2 Members ([Product] and [Selling Company]) and 1 Measure ([Qty Sold]). I would like an output to be a table separates the amount of items sold for each product by Company A from all the other companies selling the product. I then want the table to show (1) the total number of items sold by 'Company A' and all other companies combined, and (2) calculate the % of each product sold by 'Company A' compared to all other companies combined.

Final Output I want:

                   Company A       All Other Companies
               Sold     % Total     Sold      % Total
              ------------------   -------------------   
Product 1        20     36.4%         35        63.6%
Product 2        10     20.0%         40        80.0%
Product 3        60     42.9%         80        57.1%

Here are my steps:

1. Created a member called [Not Company A] using a query calculation and the except function to remove company A:
except([All Companies],[Company A]

2. Used a total function to create a new member [Other Companies]:
total(CurrentMeasure within set [Not Company A])

3. Created a calculated set called [AdjustedSellingCompany] that joins the member Company A and the new member Not Company A:
set(member([Company A],'1','Company A'),member([Other Companies],'2','All Other Companies'))

4. Once this is done, I created a calculated measure '% Total':
[Qty Sold]/total([Qty Sold] within set [Qty Sold])

Instead of the report I am hoping for, I get the following:


                   Company A       All Other Companies
               Sold     % Total     Sold      % Total
              ------------------   -------------------   
Product 1        20     50.0%         35        50.0%
Product 2        10     50.0%         40        50.0%
Product 3        60     50.0%         80        50.0%


HELP!! What am I doing wrong??? These steps work when I use a set when I drag items directly from the cube. But when I am building my own sets, I am running into problems.

I am working in the Cognos 10 enviornment using Cognos Report Studio with a TM1 cube.

Any help would be much appreciated.

[MFGF]

Hi,

First thought - what is [All Companies] - is it a level, a set or a member? It looks suspiciously like it might be the root member of the Companies hierarchy? If so your except() will not work - you would have to get the children of [All Companies] and except [Company A] from this set.

Why are you creating a new set calles AdjustedSellingCompany? Won't this be the same set of members as [All Companies] (or children([All Companies]) if my hunch above was correct)?

Wouldn't your percentage expression be:

[Qty Sold]/total([Qty Sold] within set [AdjustedSellingCompany])

(or within set [All Companies] (or children([All Companies])) depending on the above)?

MF.

[antya76]

Outstanding. I was making this way more complicated then necessary!