Pivot Table Totals not always displayed

[#1]

I have a pivot table as below which is only showing a small extract of the data. The problem I have is that unless I really filter in on the data the total for Value Added is not displayed.

product	fin_year	Value GBP	Value Added
MBEU179090		9934	7328
MBEU179189		8753	7861
MBEU179444		4224	3401
MBEU179445-1		2390	1991
MBEU179602-1		3599	3061
MBEU179672		3223	2606
MBEU185143		1570	1414
MBEU200440		18351	14652
MBEU200543		6716	5134
MBEU205111		1149	1149
MBEU205118		4460	4460
MBEU215103		1098	1098
Total		65466

If I select each product in turn then the total is displayed

product	fin_year	Value GBP	Value Added
MBEU179672		3223	2606
Total		3223	2606

Any ideas what is causing this, the Value GBP field is setup in the same was but always returns a value

[#2]

How is the expression?

often the expression not shows the total amounts, because not sum to the dimensions, for example,when doing sums in if statements

regards

[spividori]

Hi!

You should use the function aggr.

Regards.

[Oleg_Troyansky]

I suspect you might have a part of your expression that can't be uniquely calculated at a total level, in which case you should either enclose all of your components within your aggregation function, or use aggr() to calculate the expression at a lower level of detail and then aggregate those pre-calculated results.

If you show your expression, it would be easier to pin-point the problem

[#5]

My expression as it stands is

Sum(order_book_value)-(standard_material*sum(sales_order_qty))

[Oleg_Troyansky]

Exactly what I suspected... your field standard_material may return multiple possible values at the total level, and that renders the whole expression as a null. I'd recommend 2 changes:

1. Include standard_material within the sum()

2. Use function RANGESUM, to avoid getting null() when one of the two sum() returns a null() or missing.

The end result should look like this:

RANGESUM( Sum(order_book_value), -1 * sum(standard_material*sales_order_qty))

I believe this should work.