Announcements
cancel
Showing results for
Did you mean:
Creator

## Pivot Table Partial totals not working properly.

Hi Everyone.

In the below pivot table i have enabled partial totals for ID.

I want the total to be different sum of the Top rows. but it is defaulting to Expression total.

It is working the way it should Visits as the expression is Sum(Visits).

But when it comes to AMGA and MGMA there is some multiplication going on expression level. Rather than just adding the columns the total is also being calculated.

is there a way i restrict it to sum.

Thanks

Nithin

1 Solution

Accepted Solutions
MVP

You can multiply within Aggr(), but you cannot use another column reference with Aggr(). If Weight is an expression, you need to write the whole expression again within the Aggr() function

Sum(Aggr(Sum(AMGA) * Expression4WeightHere, Division, ID))

11 Replies
MVP

You need to use Sum(Aggr(, Dimension/s)) around your expression to fix the totals... read here

Totals in Charts

Sum(Aggr(YourExpression, Division, ID))

Creator
Author

This is really helpful i am 1 step closer to the answer.

My expression in AMGA is

SUM(AMGA) * WeightWeight is the dimension and weight is also calculated expression.

When i rewrite the expression in the following manner it is giving me the error.

sum( Aggr( sum(AMGA) * Weight , Division,ID) )

but when i take out the weight it is giving me correct format but i require weight to be multiplied

sum( Aggr( sum(AMGA) , Division,ID) )

How to write the multiplication inside the aggr()?

Weight will return Row level, To work you may try this

sum( Aggr( sum(AMGA) * Sum(Weight) , Division,ID) )

OR

sum( Aggr( sum(AMGA* Weight) , Division,ID) )

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
MVP

You can multiply within Aggr(), but you cannot use another column reference with Aggr(). If Weight is an expression, you need to write the whole expression again within the Aggr() function

Sum(Aggr(Sum(AMGA) * Expression4WeightHere, Division, ID))

Creator
Author

That is also determined by PSR and FTE.

PSR*FTE/12.

i think i cant write this in side the aggr.

MVP

Anything which is not a field will not work inside Aggr(). If PSR and FTE are fields, then it will work

Sum(Aggr(Sum(AMGA) * PSR*FTE/12, Division, ID))

Creator
Author

it throws an error if i write sum( Aggr( sum(AMGA* Weight) , Division,ID) ).

I tring to put all the stuff in the backend and do the aggr

Creator
Author

ok. i will try to put all these in data modal and see

Boom, You said title it "Pivot Table". We thought you are doing this in UI. And the concept Aggr() is not the existing function in Script (So, In script we are calling Group By like DB)

Anyway, Perhaps this?

Table:

Load Division, ID, AMGA, Weight From Source;

Load Division, ID, Sum(AMGA)*Sum(Weight) as AMGAWeight Resident Table Group By Division, ID;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Community Browser