Pivot table - partial sum

[#1]

Hello.

I have constructed a pivot table and have set up a partial sum to calculate my total column

My expression is an average of my headcount. However, for my TOTAL column also works the average out.

So in the example below, instead of having 8.77 as a total, I want 16.50 + 49.4 + 2.10 + 8.00 + 3.00 = 79

How can I acheive this?

Many thanks

[#2]

Please post your expression and some sample data if possible. Without knowing more, I assume that some aggregation function (for example sum) is missing from your expression. Try adjusting that, then let me know what happened.

Best,

T

[#3]

Hi Tibor,

my expression reads as '=Aggr(Only({1< Entity = P({1< Region={'ASIA'}>} Entity) >} Entity), Region, Entity)'

I have tried encasing it within the sum expression but it didn't quite work.

[#4]

And you say that per dimensions the expression gives you correct values, but when subtotaling, the number you get is not the sum of averages, but the average of averages? It would be much of help if you could post a picture with your table and data.

[#5]

Ok , attached is an example, so instead of getting 12.5, I want  11+12+13+14 = 50

[#6]

If you are not insisting on using a pivot table, you can achive waht you want by changing the type to straight table, then on the expression tab, checking sum of rows.

[#7]

Yes I agree that this can be done in a table. However, I need to use the pivot table. In my app where my real data resides, a table isn't sufficient in terms of the layout as it is a Profit and Loss piece. Hence why I want to use a pivot.

[#8]

Try this in your expression:

if(dimensionality()<>'1',sum(Value),avg(Value))

Let me know whether it worked or not.

[#9]

Hi Tibor,

Yes in the example I gave you, its worked perfectly.

I need to figure out why it isn't working in my app now!

Thank you!!

[#10]

I think an expression like that should also work in your app. Maybe you wrote the expression incorrectly? What do you get now as a result?