Using total in a pivot table

[danielrogra]

Hi all

I have the following issue I don't know how to solve it:

I've created one fact table with two kind of data using concatenate. Most of the columns are in both subset but there is one that is only in one subset (Let's say variable A only exists in type A).

I want to create the following table:

The problem I see is that if I have let's say 20 variables in common and I want to put all of them in the pivot table I have to write the following code:

sum({<tipo={'TypeB'},VariableA=>} Total <Year,Year-Month,Variable B,Variable C,Variable D,Variable E......> total)

Is there another way to do that?

Thanks you!

[danielrogra]

I'd like to answer the following questions:

1- Is that the best way to do that?

2- The desired output would be the following (Delete the row on yellow)

3- When I've created the second measure, I've done like this: Sum({<Type={'B'},Category=>} total <year,Category_desc>Total)  because I want to display that measure by year and category_desc. How about if tomorrow I have twenty dimensions instead of 2?

Thanks in advance

[agigliotti]

if you need the pivot table as you created, i guess there is not other way.

[swuehl]

Maybe like discussed above (two tables linked):