Recreating Discounted Value
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:
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!
Recreated Pre-Discounted Totals
Lightspeed Analytics and reporting consultant