ABC (Pareto) Analysis and RFM Customer Segmentation in Power BI | Free Power BI Course: From Beginner to Advanced

What you'll learn

Calculate the cumulative percentage of sales and build a Pareto chart.
Classify products into A, B and C with DAX, as a measure and as a column.
Segment customers with the RFM model: recency, frequency and monetary value.
Turn these analyses into concrete sales decisions.
Compare an ABC classification by sales with one by gross profit.

📥 Practice this lesson

The results in this lesson cover the whole file period (2025 and 2026), with no filters.

summit-sales-power-bi.xlsx · the Summit Supply model

A sales VP doesn't have time to review 12 products and 40 customers one by one. She needs to know where to focus: which products can never run out, which customers are the most valuable and which ones are about to leave. Two classic analyses answer those questions, and with the DAX you already know (Lessons 7 and 11) you can build both in an afternoon.

6 of 12Summit products generate 74.2% of sales

8"champion" customers: they order often, spend a lot and bought recently

$34,760in lifetime purchases from the 3 at-risk customers to call this week

The Pareto principle

In almost every business, a minority of products or customers generates most of the sales: the 80/20 rule. It isn't an exact law, but the pattern shows up again and again. ABC classification turns it into a tool:

Class	Usual rule	What to do
A	Products that add up to the first 80% of sales	Never let them run out: safety stock, the best supplier terms, weekly tracking
B	From 80% to 95%	Standard management, monthly review
C	The remaining 5%	Decide whether they're worth keeping, cut inventory, sell them in bundles

Cumulative percentage in DAX

For each product, add its sales plus the sales of every product that sells more than it does, and divide by the total:

Cumulative % Sales =
VAR ProductSales = [Total Sales]
VAR Total =
    CALCULATE ( [Total Sales], ALLSELECTED ( Products[Product] ) )
VAR Cumulative =
    SUMX (
        FILTER (
            ALLSELECTED ( Products[Product] ),
            [Total Sales] >= ProductSales
        ),
        [Total Sales]
    )
RETURN
    IF ( NOT ISBLANK ( ProductSales ), DIVIDE ( Cumulative, Total ) )

ALLSELECTED removes the row's product filter but respects the page's slicers: if you filter to the South region, the Pareto uses only the South.
Inside FILTER, [Total Sales] is evaluated for each product thanks to context transition (Lesson 7).

ABC Class =
VAR p = [Cumulative % Sales]
RETURN
    SWITCH ( TRUE (), ISBLANK ( p ), BLANK (), p <= 0.80, "A", p <= 0.95, "B", "C" )

In a table with product, sales, cumulative percentage and class, sorted by sales from highest to lowest:

Product	Sales	Cumulative %	Class
Long-grain rice 20 lb	$90,809	17.6%	A
Whole milk 1 gal, 4-pack	$81,717	33.4%	A
Ground coffee 30 oz	$73,670	47.7%	A
Cheddar cheese 2 lb	$58,745	59.1%	A
Vegetable oil 1 gal	$40,018	66.9%	A
Cola 12-pack	$38,008	74.2%	A
Butter 1 lb	$33,461	80.7%	B
Greek yogurt 32 oz	$29,819	86.5%	B
Orange juice 52 oz	$25,877	91.5%	B
Granulated sugar 4 lb	$17,401	94.9%	B
Spring water 24-pack	$15,220	97.8%	C
Spaghetti 2 lb	$10,717	99.9%	C
(Blank)	$508	100.0%	C

How to read it: half the catalog (6 of 12 products) generates 74% of sales. Rice, milk and coffee alone make nearly half. And the "(Blank)" row is the 3 sales with code P99 you found in Lesson 5: a reminder that dirty data shows up in every analysis.

The Pareto chart

Use a line and clustered column chart: Product on the X-axis, Total Sales as columns (sorted from highest to lowest), and Cumulative % Sales as a line on the secondary axis. Add a constant line at 80% from the Analytics pane. It's one of the best-known charts in leadership meetings. With Summit's data it looks like this:

Class A 6 products74.2% of sales.
Class B 4 productsThey take the cumulative total to 94.9%.
Class C 2 productsSpring water and spaghetti.
Cumulative % lineCrosses 80% between the sixth and seventh product.

Pareto of 2025–2026 sales, without the "(Blank)" row for the P99 sales ($508).

1. Why use ALLSELECTED instead of ALL to calculate the total?

Because ALL doesn't exist in DAX.
So the Pareto respects the page's slicers (for example, a region or a year) and recalculates within that selection.
Because ALLSELECTED is faster.

With ALL, the total would always be the whole company's even when you filter a region, and the percentages wouldn't reach 100%.

Measure or column?

A measure can't be used as a chart axis or in a slicer. If you want to count "how many products are in each class" or filter to "A products only", create the classification as a calculated column in the Products table:

ABC Class Fixed =
VAR ProductSales = [Total Sales]
VAR Total = CALCULATE ( [Total Sales], ALL ( Products ) )
VAR Cumulative =
    SUMX ( FILTER ( ALL ( Products ), [Total Sales] >= ProductSales ), [Total Sales] )
VAR p = DIVIDE ( Cumulative, Total )
RETURN
    SWITCH ( TRUE (), p <= 0.80, "A", p <= 0.95, "B", "C" )

The difference: the column is calculated once, on refresh, over the whole period, and doesn't change with filters. The measure is dynamic. Use the column to segment and the measure to analyze periods or regions.

ABC by gross profit: the other half of the story

Selling a lot isn't the same as earning a lot. Duplicate the measure, swap [Total Sales] for [Gross Profit] (Lesson 6), and you get an ABC class by profit. At Summit, three products change class:

Product	Class by sales	Gross margin %	Class by profit	What it means
Butter 1 lb	B	30.5%	A (78.1% cumulative)	Earns more than its sales suggest
Spring water 24-pack	C	38.2%	B (93.9%)	The highest margin in the catalog
Granulated sugar 4 lb	B	29.7%	C (97.4%)	Sells, but contributes little profit

The top of the ranking changes too: coffee sells $17,138.74 less than rice but earns almost the same gross profit ($23,773.61 vs. $23,818.51), because its margin is 32.3% vs. rice's 26.2%. Before you push or drop a product, look at both classifications together.

Customer segmentation with RFM

The RFM model classifies customers with three questions:

Recency: how many days since their last purchase?
Frequency: how many orders have they placed?
Monetary: how much have they spent in total?

Create these calculated columns in the Customers table:

Last Purchase = CALCULATE ( MAX ( Sales[OrderDate] ) )

Recency (days) =
DATEDIFF ( Customers[Last Purchase], MAX ( Sales[OrderDate] ), DAY )

Frequency = CALCULATE ( DISTINCTCOUNT ( Sales[OrderID] ) )

Monetary = [Total Sales]

RFM Segment =
SWITCH (
    TRUE (),
    Customers[Recency (days)] > 30, "At risk",
    Customers[Frequency] >= 60 && Customers[Monetary] >= 14000, "Champion",
    "Regular"
)

CALCULATE ( MAX ( Sales[OrderDate] ) ) in a calculated column triggers context transition: it returns that customer's last purchase.
MAX ( Sales[OrderDate] ) without CALCULATE, by contrast, returns the latest date in the whole table: that's the reference date (12/31/2026 in the file). In a model with current data you could use TODAY ().
The thresholds (30 days, 60 orders, $14,000) come from the data's distribution: the median customer placed 54 orders and spent about $12,600.

Champion · 8 customers

60+ orders, $14,000+ in purchases and a last order within 30 days.

Action: protect them. Priority service, preferred terms, ask for referrals.

Regular · 29 customers

They order normally but don't reach the champion thresholds.

Action: grow them with A products they don't buy yet.

At risk · 3 customers

More than 30 days without an order, when the average customer orders about every 13 days.

Action: win them back. A call from their rep this week.

The three at-risk customers are Corner Market 23 (72 days without an order), Family Market 8 (58 days) and Family Convenience 38 (41 days). The last one is the most serious alert: it placed 68 orders, spent $16,579.68 and was Summit's largest customer in 2025. A report that shows it in red is worth more than ten total-sales charts: it gives the rep a specific call to make today.

2. In the Last Purchase column, what would happen if you wrote MAX ( Sales[OrderDate] ) without CALCULATE?

Every customer would get the same date: the latest in the whole table, because there's no context transition.
It would still return each customer's last purchase.
It would be a syntax error.

In a calculated column, row context doesn't filter other tables. CALCULATE turns it into filter context, and then Sales is filtered to that customer.

Calculated columns are static

The RFM segments are only recalculated when data refreshes. That's fine for a daily report, but they won't respond to slicers. If you need segments that change with a date filter, you'd build them with measures and a disconnected segments table, a more advanced technique.

3. An A-class product has frequent stockouts. What does the ABC analysis tell you?

That it doesn't matter, because it's just one product.
That it should be moved to class C.
That it's a priority: A products concentrate sales, so a stockout is expensive.

Class A exists precisely to show what can't run out. A stockout on an A product loses far more sales than one on a C product.

4. Spring water is class C by sales (just 2.9% of the total). The purchasing manager wants to drop it from the catalog. What do you say?

Yes: C products should always go.
It doesn't matter, because ABC only applies to inventory.
Check the margin first: it has the best margin in the catalog (38.2%) and is class B by gross profit.

Class C by sales means low revenue, not low profitability. By profit, water moves up to B; dropping it would give up $5,815.54 of gross profit. ABC starts the conversation; it doesn't end it.

Common ABC and RFM mistakes

Classifying by sales only and dropping every C product.

Cross-check with gross profit and the product's role (a C product may bring in A customers).

Using ALL in the Pareto so percentages don't reach 100% when filtering a region.

ALLSELECTED so the analysis respects the user's selection.

Copying RFM thresholds from an article (30 days, 60 orders) without looking at your data.

Calculate your own medians and purchase intervals: at Summit, a customer orders about every 13 days.

Handing over the at-risk list without an owner.

Each customer with an assigned rep and a date for the call.

Practice exercise: customer ABC and an alerts page

Adapt the cumulative percentage measure to customers (use Customers[Customer]) and add the ABC class.
Build a "Sales alerts" page with a table of at-risk customers (segment, recency, frequency, monetary) and red conditional formatting on recency.
Add a card with the lifetime purchases of the at-risk customers.

See solution

Cumulative % Customers =
VAR CustomerSales = [Total Sales]
VAR Total = CALCULATE ( [Total Sales], ALLSELECTED ( Customers[Customer] ) )
VAR Cumulative =
    SUMX (
        FILTER ( ALLSELECTED ( Customers[Customer] ), [Total Sales] >= CustomerSales ),
        [Total Sales]
    )
RETURN IF ( NOT ISBLANK ( CustomerSales ), DIVIDE ( Cumulative, Total ) )

Customer ABC Class =
VAR p = [Cumulative % Customers]
RETURN SWITCH ( TRUE (), ISBLANK ( p ), BLANK (), p <= 0.80, "A", p <= 0.95, "B", "C" )

At-Risk Sales =
CALCULATE ( [Total Sales], Customers[RFM Segment] = "At risk" )
→ $34,760.28 ($16,579.68 + $10,919.95 + $7,260.65)

Notice that customers are far less concentrated than products: with 40 customers of similar size, it takes 29 of them to reach 80% (29 in A, 8 in B and 3 in C). ABC analysis tells you how closely your business follows the 80/20 rule, or doesn't.

Summary

Cumulative percentage ranks products or customers by contribution; cutoffs at 80% and 95% give classes A, B and C.
ALLSELECTED makes the Pareto respect slicers; context transition evaluates each item.
Use a measure for dynamic analysis and a calculated column to segment and count.
RFM combines recency, frequency and monetary value to find champions and at-risk customers.
A good analysis ends in a concrete action: stock for A products, a call for at-risk customers.
Classify by gross profit too: at Summit, butter moves up to A and spring water to B.

Next lesson: Power BI PL-300 Certification: Exam Topics, Study Plan and Portfolio. In the course's final lesson you'll prepare for Microsoft's official certification and build a portfolio that helps you get hired.