Recreating Discounted Value

josephmckeownjosephmckeown Moderator, Lightspeed Staff Posts: 80 moderator

Sometimes we may want to determine what the full-dollar value of discounted dollars was from sales. There are a few easy calculations that we can add to any Sales report to re-create what the discounted value was.

Spoiler Alerts Below

Step One: Launch Report

To begin, we just need a Sales Report, such as Recent Sales


Step Two: Required Data

There are two measures we need on a report to recreate the dollar value of discounts:

The Number of Sales Lines:

(It's important that we're using # of Sales Lines and not Quantity Sold!)

and the Sale Line Average Discount

And let's run the report to get the needed data:

So far so good!


Step Three: Adding the Calculation

So next, we want to add the calculation to create the desired discounted value. So we'll go to our Custom Fields, and select a New> Table Calculation


Which will open up our Edit Table Calculation window:


...so let's look for the # of Sales Lines by starting to type "Lines"

I see it at the top, so we'll click on it...


next we'll add a star to indicate that we'll be multiplying...

...finally, we'll look for "discount"


...again, I see "Avg Discount" near the top, so I'm going to click on it.


Let's also make a few practical changes: I'll switch the "Default Formatting" to "U.S. Dollars"


...and let's also give this a better title:


I'm calling it "Recreated" Discounts because it's not reading Retail data first-hand, but it is logically creating this value from the data that we do see.

And we'll save

So if I've done everything properly, I should get a green Calculation field to the right of my reporting data:

Looks good!

Step Four: Hiding excess Data

Now I may not actually be interested in seeing the "Sales Lines" or "Avg Discount" data on my final report.

What I can do to hide these data points is, by hovering over their title, and clicking on their gear icon...

When we do this, we'll see the option to "Hide from Visualization". Let's click on that.

and we'll do the same for "Avg Discount"


So now, if we look at the report in the Visualiation:


We'll see the results of the Calculated field, but not the raw data being used to generate it!


Spoiler Alerts

Recreated Discounts

${sale_lines.count}*${sale_lines.discount_avg}

Recreated Pre-Discounted Totals

${sale_lines.total_sales_no_tax}+(${sale_lines.count}*${sale_lines.discount_avg})



Calculations Home

Joseph McKeown
Lightspeed Analytics and reporting consultant
Lightspeed HQ
Sign In or Register to comment.