Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Year
Week (with Partial Sum checked)
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
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)
I'm sorry, I'm new at this but I don't understand what you mean by 'your dimensions' (at the end)
It means all the dimensions that you mentioned above -
YearWeek (with Partial Sum checked)
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]
)