Analytics Calculations: Starting (Average Sales by dimension)

josephmckeownjosephmckeown Moderator, Lightspeed Staff Posts: 80 moderator
edited May 2019 in Reporting & Analytics
Anyone can build custom calculations onto a Lightspeed Analytics report. Don't believe me? Well let's try one together. Let's find the average sale per category this week.

To start, let's launch a Recent Sales by Category report, which should include the Top Level Category of sales, as well as measures of Total, Profit, and Margin.

Next, we'll need to add the measure of "Quantity Sold". On the left-hand side of Analytics, look for the "Sale Line" dimensions and measures. Under its "Totals", select "Quantity Sold", and then click on the purple "Run" button in the top right-hand corner to pull the sale-line quantity per top-level category.

Now the fun starts, above the Dimensions and Measures, you will see the top drop-down menu for "Custom Fields", click on "New", and you will get the option for a new "Table Calculation"

Then click on  "Table Calculation" to open to the table calculation editor...

To find the average sale total, we need to divide the total sales by the quantity sold. These are two measures on the table now, so we can build a calculation that references them by name. To add them to our calculation, we just need to start typing the column names:

Let's start with Sale Line Total, I'm going to start typing the word, "Total", as I do, I'll see options come up in the calculation field:

The option I'm looking for is "${sale_lines.total_sales_no_tax}" I can select it from the available options, and then it will show up in my calculation field...

Next, I need to add the divide function, which is simply a forward slash symbol: "/" (you can add spaces between the columns and the functions, the calculation will still work)

Finally, I need to complete the calculation by dividing by Quantity Sold, so again, I will start typing "Quantity" in the calculation, this will show up as: "${sale_lines.unit_total}"

Now, if I've prepared the calculation properly, then when I click "Save Table Calculations"...

...a new green column will show up on the table called "Calculation 1"

...which shows me the Sale Line total divided by the Sale Line quantity.

Now, the decimals make this calculation difficult to read quickly, so let's go back to the grey "Calculations" button, and change the formatting of the number from "Default Formatting" to "U.S.Dollars"

Also, let's change the title of the calculation from "Calculation 1" to "Average Sale"

So now when I save it, the calculation is clear to understand and easy to read!

Now, if our rows are complete on the table, we can sort by calculated results.

Now, if I made an error in my preparation of the calculation, i will get an error in the results:

...but there are helper messages in the calculation field that may identify the error. Common errors may involve missing columns, columns whose titles have changed, formulae that have not been entered correctly:

Did it work?

*Now you can build Averages for any kind of dimension, Average sales by Vendor, by Employee, by Month and by Store etc. We could turbo-charge this report by adding pivots on averages and adding comparisons, or comparing Averages to one another over time...

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