Excluding a dimension value in a pivot table?

[#1]

Hello,

I've a problem with a Set Analysis expression and no idea how to resolve it.

I created some basics data for testing as follow:

Test:

LOAD * INLINE [

    Project, Cost

    Saturne, 1

    Jupiter, 1

    Neptune, 1

    Pluton, 1

];

Now, I'm trying to have a table as :

Column 1: Project

Column 2: Sum(Cost) (with this example, it's 1).

Column 3: Sum(Cost) of all other projects (or in other words, All projects except the current one) (it should be 3).

I can't find any solution with set analysis or other method to find on line 1 the sum for project Jupiter, Neptune and Pluton. I can't actually select {All Projects minus/except Saturne}.

I hope you find this clear. Anyone have an idea for helping?

Thank you!

[sbaldwin]

Hi,  have you tried the formula:

=sum(TOTAL Cost) - sum(Cost)

?

Thanks

Steve

[#3]

Thanks for your reply.

My bad! My example is way too simple for modelling my problem. In my real model, I've others dimensions that must not be ignored for the cost aggregation.

But your answer, in its simplicity, might help me if I can have this TOTAL depending on my others dimensions but not the dimension "Project".

I'll post my solution if I find one. Until then, I'm happy to have new ideas.

[#4]

Just a follow-up I've never took take to post.

My understanding is that in a table, you can't actually select other values of a field specified in a rox of the table, which implied my problem. 

My solution consisted in modifying my script so I can have this value directly.

I don't know if it's the best one, but it worked in my case.

Thanks for your help.