Sample report
Deep discounts wiped out nearly a third of this store’s profit.
This is a Diagnose report. I don’t have clients yet, so I ran it on Superstore, a practice dataset Tableau publishes. It’s not a real company’s books, but every number here is calculated from the actual file. The method is at the bottom, so you can check each one.
Sample analysis — public retail dataset
How I calculated thisTHE SHORT VERSION
- About one order line in seven was discounted above 20%. Those lines lost $135,376.
- Three product lines made 73% of that loss: binders, tables and machines.
- Fix first: stop the 70–80% deals on binders and appliances. Then cap discounts at 20%, and measure what it does to profit.
Finding 1
Past 20% off, every $100 sold loses money.
Up to 20% off, every discount level makes money overall. At 30% off, each $100 sold loses $10. At 80% off, it loses $180.
Only 32 of the 1,393 lines discounted above 20% made any profit at all.
Sample analysis — public retail dataset
- profit on lines discounted above 20%
- −$135,376
- order lines discounted above 20% (13.9% of all)
- 1,393
- of the profit every other line made, wiped out
- 32.1%
Finding 2
All three categories lose money past 20%.
It isn’t one category. Furniture, Office Supplies and Technology all make money at full price and at up to 20% off. Above 20%, all three go negative.
Office Supplies is the worst. Above 20% off, it loses $119 for every $100 sold.
Sample analysis — public retail dataset
Finding 3
Three product lines make 73% of the loss.
Binders, tables and machines. That’s where to look first.
Sample analysis — public retail dataset
Profit on lines discounted above 20%, by product line
- Binders613 lines−$38,510
- Tables176 lines−$30,698
- Machines53 lines−$29,555
- Bookcases70 lines−$11,098
- Appliances67 lines−$8,630
- Chairs158 lines−$6,737
- Phones109 lines−$6,386
- Furnishings138 lines−$5,945
- Copiers9 lines+$2,183
And three states make 54% of it.
Texas, Ohio and Illinois, and all three lose money overall. In Texas and Illinois, over 40% of order lines are discounted above 20%. In the West region, under 4% are.
Share of the loss on lines discounted above 20%, by state
What I’d tell the owner
Fix these first, in this order.
The clearest loss first, then the big-ticket items, then a test you can measure.
Fix 1: Stop the 70–80% deals on binders and appliances.
680 order lines lost $47,140. Every $100 of those sales lost $119. The 80% deals ran only in Texas and Illinois.
Why first: Of the three, it loses the most per dollar sold, and it’s the easiest change to make.
Fix 2: Cap tables and machines at 20% off.
229 order lines lost $60,254. These are big-ticket items: each line averaged $728 in sales, almost three times the average deep-discount line ($260).
Why second: Fewer orders to change, and each one is worth more.
Fix 3: Start the cap in Texas, Ohio and Illinois.
These three states carry 54% of the loss, and all three lose money overall.
Why third: Three states first gives you a clean comparison with everywhere else.
The catch
The data shows what the discounts cost. It can’t show how many of those orders would still happen at 20% off. So I’d run the cap for a month, then compare profit, not just sales, before rolling it out.
What this data can’t tell you
- Whether those orders happen without the discount.
- Why the discounts were given: clearance, a big customer, or habit.
- Trends over time. This version of the file has no order dates.
Method
How I calculated this.
So you can check every number yourself.
Sample analysis — public retail dataset
- Source
- A 13-column copy of “Sample - Superstore”, the practice dataset Tableau publishes: 9,994 US order lines. Tableau’s own file has more columns, including order dates. Every figure on this page comes from this copy.
- Columns used
- Category, Sub-Category, State, Region, Discount, Sales, Profit. Nothing else.
- Totals
- All 9,994 lines: sales $2,297,200.86, profit $286,397.02.
- Duplicates
- 17 rows are exact copies of another row. This file has no order IDs, so I can’t tell a repeat from two identical orders. I kept them. Without them, the headline share is 32.11% instead of 32.10%.
- The filter
- “Above 20%” means Discount greater than 0.20, so exactly 20% counts as 20% or less. 1,393 lines pass: sales $362,770.15, profit −$135,376.06. The other 8,601 lines made $421,773.08.
- Formulas
- Margin = total profit ÷ total sales for a group. “Per $100 sold” = margin × 100. “Nearly a third of the profit” = 135,376.06 ÷ 421,773.08 = 32.1%. “Share of the loss” = a group’s profit ÷ −$135,376.06.
- Exceptions
- Two small groups break the pattern. Copiers above 20% off (9 lines, all at 40%) made $2,183. Machines at exactly 30% off (5 lines) made $326. So the finding holds for each category as a whole, not for every product line inside it.
- Rounding
- Dollar figures in the text are rounded to the nearest dollar. Percentages are rounded to one decimal place, or to the nearest whole percent in headlines. The tables below are exact.
Check it yourself in Google Sheets or Excel
- 1Open the file. In the 13-column version, Discount is column L and Profit is column M. Change the letters if yours differ.
- 2
=COUNTIFS(L:L, ">0.2")returns 1,393. - 3
=SUMIFS(M:M, L:L, ">0.2")returns −135,376.06. - 4Swap
">0.2"for"<=0.2"and the same sum returns 421,773.08.
All the figures
Exact to the cent. Open a table to check any number on this page.
A. By discount level
Every order line, grouped by discount.
| Discount | Order lines | Sales | Profit | Per $100 sold |
|---|---|---|---|---|
| 0% | 4,798 | $1,087,908.47 | $320,987.60 | +$29.51 |
| 10% | 94 | $54,369.35 | $9,029.18 | +$16.61 |
| 15% | 52 | $27,558.52 | $1,418.99 | +$5.15 |
| 20% | 3,657 | $764,594.37 | $90,337.31 | +$11.82 |
| 30% | 227 | $103,226.65 | −$10,369.28 | −$10.05 |
| 32% | 27 | $14,493.46 | −$2,391.14 | −$16.50 |
| 40% | 206 | $116,417.78 | −$23,057.05 | −$19.81 |
| 45% | 11 | $5,484.97 | −$2,493.11 | −$45.45 |
| 50% | 66 | $58,918.54 | −$20,506.43 | −$34.80 |
| 60% | 138 | $6,644.70 | −$5,944.66 | −$89.46 |
| 70% | 418 | $40,620.28 | −$40,075.36 | −$98.66 |
| 80% | 300 | $16,963.76 | −$30,539.04 | −$180.03 |
| All | 9,994 | $2,297,200.86 | $286,397.02 | +$12.47 |
B. By category and discount
Margin = profit ÷ sales, for each category and discount band.
| Category | Discount | Order lines | Sales | Profit | Margin |
|---|---|---|---|---|---|
| Furniture | No discount | 836 | $256,025.27 | $58,133.08 | 22.71% |
| 1–20% off | 743 | $290,823.78 | $14,795.95 | 5.09% | |
| Over 20% off | 542 | $195,150.74 | −$54,477.76 | −27.92% | |
| Office Supplies | No discount | 3,129 | $442,150.00 | $130,506.11 | 29.52% |
| 1–20% off | 2,217 | $237,373.89 | $39,124.83 | 16.48% | |
| Over 20% off | 680 | $39,523.15 | −$47,140.14 | −119.27% | |
| Technology | No discount | 833 | $389,733.20 | $132,348.42 | 33.96% |
| 1–20% off | 843 | $318,324.57 | $46,864.69 | 14.72% | |
| Over 20% off | 171 | $128,096.26 | −$33,758.16 | −26.35% |
C. Above 20% off, by product line
Only lines discounted above 20%. Share = the product line’s profit ÷ −$135,376.06.
| Product line | Category | Order lines | Sales | Profit | Share of loss |
|---|---|---|---|---|---|
| Binders | Office Supplies | 613 | $36,140.61 | −$38,510.50 | 28.45% |
| Tables | Furniture | 176 | $89,956.54 | −$30,698.22 | 22.68% |
| Machines | Technology | 53 | $76,839.11 | −$29,555.35 | 21.83% |
| Bookcases | Furniture | 70 | $28,544.00 | −$11,097.76 | 8.20% |
| Appliances | Office Supplies | 67 | $3,382.53 | −$8,629.64 | 6.37% |
| Chairs | Furniture | 158 | $70,005.50 | −$6,737.12 | 4.98% |
| Phones | Technology | 109 | $34,337.35 | −$6,385.79 | 4.72% |
| Furnishings | Furniture | 138 | $6,644.70 | −$5,944.66 | 4.39% |
| Copiers | Technology | 9 | $16,919.80 | +$2,182.98 | −1.61% |
| All | 1,393 | $362,770.15 | −$135,376.06 | 100.00% |
D. Above 20% off, by state
Only lines discounted above 20%. Texas lost $25,729.36 overall; Ohio lost $16,971.38 overall; Illinois lost $12,607.89 overall.
| State | Order lines | Sales | Profit | Share of loss |
|---|---|---|---|---|
| Texas | 415 | $91,590.01 | −$33,955.28 | 25.08% |
| Ohio | 179 | $49,480.73 | −$20,227.22 | 14.94% |
| Illinois | 228 | $37,544.09 | −$18,436.99 | 13.62% |
| The other 16 states | 571 | $184,155.32 | −$62,756.56 | 46.36% |
| All | 1,393 | $362,770.15 | −$135,376.06 | 100.00% |
Find out what your numbers can tell you.
Send me your data. Within 5 working days, you’ll know if it’s reliable and what it can answer. Pay nothing.
Rather email? contact@deepanshujoshi.com