Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have hopefully simplified my problem with calculating commission per period, but just showing a Total
I can show the commissions on a sale, per salesperson, per period.
Period | Sales Person | Gross Sales | Commission | |
---|---|---|---|---|
January | Mary | £1,000 | 50 | |
February | Mary | £2,000 | £150 | |
March | Mary | £5,000 | £450 | |
I would like a separate table that simply sales and Commission
Sales Person | Gross Sales | Commission |
---|---|---|
Mary | £8000 | 650 |
Whilst i can simply do Sum(Gross Sales) for Gross Sales.
Because the Commission is say 5% on the first £1000 per month, then 10%. I need to out commission per period, then Sum the totals
What function should I do to get the Commission to work it out per period, then total?
Thanking you in advance.
Is Sum(Commission) didn't work for you?
Thanks for the quick reply.
in my data I only have the Gross Sales, and the commission is worked out in a Table, so I don't have a lists of Commission Date to Sum.
Thus I can work out the commission per given period,
But I can not have a "summary table"
may be this
Sum(Aggr(Commission,Period))
May be just this
Table:
LOAD Period,
SalesPerson,
GrossSales,
Commission
FROM ...;
AggrTable:
LOAD SalesPerson,
Sum(GrossSales) as AggrGrossSales,
Sum(Commission) as AggrCommission
Resident Table
Group By SalesPerson;
If i understood correctly, I believe you have Commission Percent for each period and you want to calculate the commission amount. If that is the case may be this
Sum(Aggr((Gross Sales*Commission)/100,Period))
I have in my data, a list of sales, and the day they occurred, and the salesperson.
So I can use basic functions, to total sales per month per sales person.
The commission calculations is on a sliding scale.
So if I break sales down into per month and apply the sales, then I get the commission per month, which the table can total.
I would like a separate table for the total year.
Easy to work out sales for the year, by just
DIM Measure
Salesperson SUM(GROSS SALES)
But if I apply the commission formula it will work it out on the Totaled Gross sales, whereas I would like it to work out Commision per month, then totaled
if that makes sense.