Getting a Total in a Pivot table

[Anonymous]

We have several Salespeople who get paid by the unit sold, but it is a different amount for different items; I am trying to calculate their pay.

I have a Pivot Table with the following Dimensions;

1. Customer.Sales Rep Full Name (with Partial Sum checked)

Year

Week (with Partial Sum checked)

1. Transactions.Item Full Name
2. Transactions.Descriptions

And the following Expressions;

Amount of Transactions – count(Distinct [Transactions.Ref Number])

# of units sold - sum(if([Transactions.Txn Type]='Credit Memo', -Transactions.Quantity, Transactions.Quantity)) 

Commissions – if ([Transactions.Item Full Name]=1 or [Transactions.Item Full Name]=2,
(sum(if([Transactions.Txn Type]='Credit Memo', -Transactions.Quantity, Transactions.Quantity)) *.15),
if ([Transactions.Item Full Name]=3,
(sum(if([Transactions.Txn Type]='Credit Memo', -Transactions.Quantity, Transactions.Quantity)) *.25)

This works accurately on each line but I can’t get a total for the commissions unless I select only 1 item, I do get an accurate total for Transactions and Units.

Thank You

[neelamsaroha157]

May be you can try the aggregation function -

Commissions – Aggr(if ([Transactions.Item Full Name]=1 or [Transactions.Item Full Name]=2,
(sum(if([Transactions.Txn Type]='Credit Memo', -Transactions.Quantity,Transactions.Quantity)) *.15),
if ([Transactions.Item Full Name]=3,
(sum(if([Transactions.Txn Type]='Credit Memo', -Transactions.Quantity,Transactions.Quantity)) *.25), your dimensions)

[Anonymous]

I'm sorry, I'm new at this but I don't understand what you mean by 'your dimensions' (at the end)

[neelamsaroha157]

It means all the dimensions that you mentioned above -

1. Customer.Sales Rep Full Name (with Partial Sum checked)

        YearWeek (with Partial Sum checked)

1. Transactions.Item Full Name
2. Transactions.Descriptions

Aggr(if ([Transactions.Item Full Name]=1 or [Transactions.Item Full Name]=2,
(sum(if([Transactions.Txn Type]='Credit Memo', -Transactions.Quantity,Transactions.Quantity)) *.15),
if ([Transactions.Item Full Name]=3,
(sum(if([Transactions.Txn Type]='Credit Memo', -Transactions.Quantity,Transactions.Quantity)) *.25), [Customer.Sales Rep Full Name], [YearWeek] , [Transactions.Item Full Name], [Transactions.Descriptions]

)