Need tips on pivot table sum or sum of dimension in pivottable

[#1]

Hi,

I have the following pivottable as shown below. My challenge here is that Open Order Qty should ideally only be shown one time per position in the pivottable. Open order qty for one position can be split into several deliveries (supply qty).

So either I need:

1) To make total sum of Open Order Qty of dimension, or

2) Find a way to show Open Order Qty only one time per position in the pivot table expression.

Any input highly appreciated.

Best regards,
Freddy

[CELAMBARASAN]

you could try something like

if(Alt(RowNo(), 0) = 0 or RowNo() = 1, Sum(OpenOrderQty))

[#3]

Hi, I tried to copy this formula but it did not work. Get the same result as shown in the picture.

[CELAMBARASAN]

could you please attach a sample?.

[#5]

Here is a sample data set.

[tresesco]

May be like attached sample? It is all about placing at the right position.

[#7]

Thanks, but what I am trying to achieve is that the total sum of all open order qty fields should match the sum of every field.

So if you look at open order qty in the dimension part - it is possible to export to excel and sum this correctly. The result will be 130. If you look in the expression part the sum total is correct in the pivot table (130) but it is confusing that if you sum the fields you get 170 which is because open order qty is shown for every record.

[tresesco · Accepted solution]

May be like this? PFA

[#9]

Thanks a lot! That did it!