Cumulative % of total (Pareto)
Rank items by sales and show the running share of the total, to see which items make up 80% of sales.
Cumulative % of Total =
VAR ProductSales = [Total Sales]
VAR CumulativeSales =
SUMX(
FILTER(
ALLSELECTED(Product[Product Name]),
[Total Sales] >= ProductSales
),
[Total Sales]
)
VAR AllSales =
CALCULATE([Total Sales], ALLSELECTED(Product[Product Name]))
RETURN
DIVIDE(CumulativeSales, AllSales)How it works
For each product, the measure adds the sales of every product that sold the same amount or more, then divides by the total of all products currently selected. Sorting the visual by sales descending gives the classic Pareto curve.
ALLSELECTED keeps the products allowed by slicers but ignores the row of the visual, so every row sees the same list of products.
What your model needs
- A Product table with a Product Name column, related to Sales. - Total Sales = SUM(Sales[Amount]) (called [Total Sales]).
Small example
Input data
Product | Total Sales
A | 500
B | 300
C | 150
D | 50Expected result
Product | Cumulative % of Total
A | 50.0%
B | 80.0%
C | 95.0%
D | 100.0%Common mistakes and limitations
- Products with exactly the same sales are counted together, so the percentage jumps for both at once. - Slow on tables with many thousands of products, because it compares every product with every other product. - Sort the visual by [Total Sales] descending; the measure itself does not sort anything.