Pivot Table: total row aggregated over dimension

[#1]

Hello,

I have attached an application to explain my problem.

I want a pivot table as below with a final row containing the two totals values of expressions (1,2,3) aggregated by the "Measure" dimension.

The measure dimension is a inline table without connexion with data. i just use it for have a "label dimension" in order to use two formula by expression dependant of the "Measure" label.

The table I want.

Category	Measure	Expr1	Expr2	Expr3
C1	MeasureA	2	0	2
	MeasureB	0	100001,4	100001,4
C2	MeasureA	1	0	1
	MeasureB	0	0	0
C3	MeasureA	1	0	1
	MeasureB	0	555	555
C4	MeasureA	0	0	0
	MeasureB	0	0	0
Total	MeasureA	4	0	4
	MeasureB	0	100556,4	100556,4

The one I obtain using "partial sum":

Category	Measure	Expr1	Expr2	Expr3
C1	MeasureA	2	0	2
	MeasureB	0	100001,4	100001,4
	Total	0	0	2
C2	MeasureA	1	0	1
	MeasureB	0	0	0
	Total	0	0	1
C3	MeasureA	1	0	1
	MeasureB	0	555	555
	Total	0	0	1
C4	MeasureA	0	0	0
	MeasureB	0	0	0
	Total	0	0	0

The total values must be processed when we do fields selection, so I can't process the totals values in the load script.

The only method I have found to have this is to use two pivot tables: one for the total, one for other rows.

But I want only one pivot table.

Could you help me?

[#2]

I had just a little problem because on Category field value selections I lost my Total row.

To solve it I just replace my previous inline table like this:

SET NULLINTERPRET=NULL;

CategoryMap:
LOAD Category, Category as CategoryMap
resident Category;
Concatenate(CategoryMap)
Load * INLINE [
Category,CategoryMap
NULL,Total
];
SET NULLINTERPRET=;

By just a list independent of data selections

CategoryMap :

LOAD Category as CategoryMap

resident Category;

Concatenate(CategoryMap)

Load * INLINE [

CategoryMap

Total

];

Then use this field CategoryMap in pivot table first dimension expression as below:

=If(CategoryMap=Category or CategoryMap='Total',CategoryMap)

So the total row don't be hidden if I select C1 and C2 Category value, for example.

And as the "Total" value isn't linked to Category field values expressions are automatically set to total value without the

If(CategoryMap='Total') condition. I have just need to set this condition on total value for expression using AGGR function, not for simple Sum and Count expression.

My row Total isn't hidden now when I do selection  on field "Category".

I just see something strange now: why C1 Exr2 measureB is 100001.4 and not 100000.95 ?

min valueB for  Id 1 and Category C1 is 0.45 and for Id 9 Category C1 it's 100000.5

Normally the sum must be 100000.95, not 100001.4

I have another question, but without link with this subject:

Is it possible to do something like this on this pivot table?:

		MyFirstSuperHeader		MySecondSuperHedaer
CategoryMap	Measure	Expr1	Expr2	Expr3
C1	MeasureA	2	0	2
	MeasureB	0	100001,4	100001,4
	MeasureA	1	0	1
C2	MeasureB	0	0	0
C3	MeasureA	1	0	1
	MeasureB	0	555	555
C4	MeasureA	0	0	0
C4	MeasureB	0	0	0
Total	MeasureA	3	0	0
	MeasureB	0	100556,4	0

Currently I made this with two horrible text object which don't autosize when my pivot table size change.

I have already tried to do some sort of inline table with headers/super headers, but I can't hide/delete column when expressions is null. I had something like this

Parent Header 1			Parent Header 2
Expr1	Expr2	Expr3	Expr2		Expr3

even with "Supress When Value is Null" checked on my "inline" dimension ParentHeader and Expressions defined with a condition like

IF(ParentHeader='ParentHeader1',***) I have all expressions on each of my horizontal dimension values.

[#3]

Hello,

Could someone help me on this 2 subjects above:

• Incorrect expression values on my table
• Super headers for specific expressions.

The first point is the most important. I tried some formula but it doesn't fix it. Perhaps I use bad syntax?

My sum expression don't display the good result.

Example attached.

[#4]

I have finaly found a formula which give me the good result.

I don't know if there are better formulas to do this.

But now I want my "total" Category field value remain when I do selection on the field Category (directly on the table or by a selection list)

In the application attached here, I have two table (aggregate 1 & 2).

If I select Category values by the list, I have always the good total value on my table aggregate1, but not on the second, aggregate2.

If I select directly on the table aggregate1 dimension, I lose the total rows.

On the second table I lose always the total values when I do selections.

Could you help me? I want the total row remain on my pivot when I select Category field values.

Thanks.