Analytics Calculations: Simple Commission

josephmckeownjosephmckeown Moderator, Lightspeed Staff Posts: 89 moderator
edited June 2018 in Reporting & Analytics
Similarly to the Simple Inventory Views where we used addition, subtraction, and division to follow Inventory levels, we can also use multiplication in Analytics. A common use of multiplication is with employee commission, where employees receive a percentage share of their total sales.

Let's start by creating a basic commission report. Open up your Employee Team Performance Report (found under the Employee Performance Reports)

On this report, we see each employee's total dollar sales, total hours worked, sales by hour, profit by hour, and basket size..,

...let's remove the measures that we don't need on this report: "Total Hours Worked", "Sales by Hour", "Profit by Hour", and "Basket Size"...

...We may base our commissions on profit instead of total sales. If you want to do this, add Sale Line> Totals> Profit

Let's click on the grey "Calculations" button the black "Data" bar, and then start looking for total Sales or total Profit (I will use "Profit" for this article, but the process would be the same for 'Total")

So let's look for "Profit"

it will show up as "${cl_employee_sales_dates.total_profit}"

Now let's suppose that employees get a 4% of profit commission. To create this, we'll add the star symbol to signify multiplication: "*", and then 0.04 to create 4%

A Note on Percentages in Analytics:
Percentages are functions of 1, so
100% is 1
50% is 0.5
5% is 0.05
0.5% is 0.005 etc...)

This will look like...

So if I've saved my calculation properly, I will get a new green column on my report called "Calculation 1"...

...but it's not easy to read yet, there are too many decimals, so let's go back to the grey "Calculations" button, and change the number formatting from "Default" to "U.S. Dollar"...

...and let's also rename "Calculation 1" to something more meaningful like "4% Commission"

When we save the formatting, it will come back up as a clear number to read and understand:

*We can turbo charge Commission reports in many different ways too:

We could use IF statements for example to create tiers of Commission (eg $1000+ returns 2.5%, $2,500+ returns 5%, and so on),
We could use IF statements to add bonuses to top sales employees, or employees with the highest basket sizes
We could create a floating commission rate that highlights how an employee's performance compares to the rest of the group
We could give different commission rates to different Categories of Products, to different Stores, or different employee Roles
We could define certain employees as managers whose commission may be implicit in their team's overall performance...

...and so on

Calculations Home
Post edited by josephmckeown on
Joseph McKeown
Lightspeed Analytics and reporting consultant
Lightspeed HQ
Sign In or Register to comment.