12 Replies Latest reply: Sep 29, 2018 1:15 PM by Stefan Wühl

# Using total in a pivot table

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!

• ###### Re: Using total in a pivot table

could you provide a sample app with mock data showing what are you trying to achieve?

• ###### Re: Using total in a pivot table

Hi Andrea.

I'll try to do it (The data I'm using I can't upload it)

Thanks!

• ###### Re: Using total in a pivot table

I've attached a simple app to show you my Issue.

Thanks!

• ###### Re: Using total in a pivot table

Maybe a link table linking two fact tables instead of concatenating all facts into one could be an alternative.

• ###### Re: Using total in a pivot table

Hi Stefan

I guess I would have the same problem as I want to show the total for each value of variable A as it only exists in the subset "Type A", but if I use variables that are in both subsets I want to show the value by those variables. Am I right?

• ###### Re: Using total in a pivot table

The idea would be to create a table with the common variables and another with the Type A only variables, linked by the common key (Year-Month?).

Not 100% sure if this solves your issue. But a selection in Type A or using Type A as dimensions should still get all other variables' values, without duplicating any values in aggregation.

• ###### Re: Using total in a pivot table

I've attached a simple app to show you my Issue.

Thanks!

• ###### Re: Using total in a pivot table

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?

• ###### Re: Using total in a pivot table

Maybe like discussed above (two tables linked):

• ###### Re: Using total in a pivot table

Any help ?

• ###### Re: Using total in a pivot table

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