Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 56 ยท DAX

Ranking, Top N and Percent of Total

Rank products with RANKX, keep the top N with TOPN, compute percent of total with ALL and ALLSELECTED, and build Pareto and running totals, with pandas stand-ins.

Sooner or later every report gets the same questions. Which five products sell best? What share of revenue does each region contribute? Which customers make up the first 80% of sales? How is the year adding up month by month? These are ranking and share calculations, and they all rest on one skill from the previous lesson: controlling the filter context with CALCULATE, ALL and friends.

This lesson covers RANKX and TOPN, the difference between ALL and ALLSELECTED for percent of total, a Pareto (80/20) analysis and a running total. If filter context still feels vague, read CALCULATE and Filter Context first.

DAX only runs inside Power BI, so the DAX here is read-only. The runnable examples are pandas stand-ins that compute the same numbers. A slicer is imitated by filtering the DataFrame by hand; real DAX does that through filter context.

Four questions, four tools

Rank RANKX numbers the rows.
Top N TOPN keeps the best few.
Share ALL or ALLSELECTED as the base.
Running total Add up everything so far.
1 / 2

The base measure and the data

Everything below uses a product table with a Category and one measure:

DAX
Total Sales = SUM(Sales[Amount])

RANKX: number the rows

RANKX(table, expression [, value [, order [, ties]]]) evaluates the expression for each row of the table, sorts the results and returns the position of the current row:

DAX
Product Rank =
RANKX(
    ALLSELECTED(Product[ProductName]),
    [Total Sales],
    ,
    DESC,
    DENSE
)

Reading it piece by piece:

  • The table. ALLSELECTED(Product[ProductName]) is the list of products the reader can currently see (more on ALLSELECTED below). The rank is relative to this list, so it adapts to slicers.
  • The expression. [Total Sales] is evaluated for each product in that list, with context transition, then the products are compared.
  • The value (third argument, empty here) defaults to the same expression for the current row.
  • Order. DESC means the largest value gets rank 1. This is also the default.
  • Ties. SKIP (the default) gives two tied products rank 3 and the next rank 5. DENSE gives 3, 3, 4 with no gap.

A common beginner problem is using ALL(Product) or the whole table when the visual shows only some products. Then the ranks start at the right place but have gaps, because hidden products also take positions. Use ALLSELECTED when you want ranks within what the user sees, and ALL when you want ranks across everything.

TOPN: keep the best few

TOPN(n, table, orderBy_expression [, order]) returns a table of the n best rows. It is not a measure by itself; you use it as a filter inside CALCULATE:

DAX
Top 3 Products Sales =
CALCULATE(
    [Total Sales],
    TOPN(3, ALLSELECTED(Product[ProductName]), [Total Sales], DESC)
)

For each cell this evaluates the top 3 products in the current selection and sums their sales. Combined with a product axis, it shows the three bars with values and leaves the others blank.

Often it is easier to filter the visual itself. In the Filters pane, choose a field's filter type Top N, type 3 and drag Total Sales into By value. Use DAX only when the Top N must move with a slicer or be used inside other measures.

There are two traps. First, ties: if the third and fourth products have the same value, TOPN returns both, so you can get more than n rows. Second, as with RANKX, the table you pass defines what "top" means: the top 3 products overall or the top 3 within a region depend on which filters ALL... removed.

An "Others" bar is a popular extension: the total minus the Top N sum.

DAX
Other Products Sales = [Total Sales] - [Top 3 Products Sales]

Percent of total: ALL, ALLSELECTED and ALLEXCEPT

Percent of total is a ratio between a cell and a denominator with some filters removed. Which filters you remove decides what "total" means. There are three useful choices:

Function Denominator is Good for
ALL(Table) or REMOVEFILTERS() Everything, ignoring slicers and the visual Share of the whole company
ALLSELECTED(Table) Everything the reader selected (slicers and page filters stay) Share of what is shown
ALLEXCEPT(Table, Table[Col]) Everything except one column's filter Share within a group, such as a category
DAX
% of All Sales =
DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Product)))

% of Selected Sales =
DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED(Product)))

% of Category =
DIVIDE([Total Sales], CALCULATE([Total Sales], ALLEXCEPT(Product, Product[Category])))

ALLSELECTED is the one that feels natural to users. If a slicer shows only the Gear category, the shares of the visible products add up to 100%. With ALL they add up to Gear's share of the company, for example 44%, which confuses many readers. Use ALL only when "of the whole company" is exactly the question.

For percent of parent in a matrix with a hierarchy (Category above Product), remove the filter on the lower level only: CALCULATE([Total Sales], REMOVEFILTERS(Product[ProductName])) returns the category total in each product row, and the ratio is the percent of parent. At the category row itself, the removal changes nothing and the ratio is 100%. Wrap it in IF(ISINSCOPE(Product[ProductName]), ...) to show blank at category level.

Running total and Pareto

A running total adds up everything from the start to the current point. Over a date axis, remove the date filter and keep only dates up to the current one:

DAX
Running Total =
CALCULATE(
    [Total Sales],
    FILTER(
        ALLSELECTED('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

MAX('Date'[Date]) is the last date of the current cell. FILTER walks the visible dates and keeps those up to it. The same idea works over products with a rank instead of dates, which is the heart of a Pareto analysis: sort the products from best to worst, accumulate their sales and see how many products it takes to reach 80% of the total.

DAX
Cumulative % of Sales =
VAR CurrentSales = [Total Sales]
VAR Cumulative =
    SUMX(
        FILTER(ALLSELECTED(Product[ProductName]), [Total Sales] >= CurrentSales),
        [Total Sales]
    )
VAR Everything = CALCULATE([Total Sales], ALLSELECTED(Product[ProductName]))
RETURN
    DIVIDE(Cumulative, Everything)

For every product this sums the sales of all products that sell at least as much, which is exactly its cumulative position when products are sorted by sales. Ties would be counted together, which is fine for most reports. An ABC class column is then a SWITCH on this percentage (A up to 80%, B up to 95%, C the rest), a pattern we meet in Common DAX Patterns.

Ranking and Top N in pandas

The stand-in below ranks eight products and shows how the tie settings change the numbers. Two products tie at 40.

Python RANKX, ties and TOPN

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice that Light and Bell share rank 6 under SKIP, and Gloves becomes 8. Under DENSE the same two still share rank 6, but Gloves is 7, with no gap. And asking for six products returns seven rows, because the sixth place is tied, which matches how TOPN treats ties.

Percent of total with ALL vs ALLSELECTED, plus Pareto and running total

This example imitates a slicer selecting the Gear category. It compares the two denominators, then builds the cumulative percentage and ABC class, and finally a running total by month.

Python ALL vs ALLSELECTED, Pareto and running total

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

With the Gear slicer, the shares against ALL add up to only 44.4%, while those against ALLSELECTED add up to 100%. The Pareto table shows that four products (the two bikes, the helmet and the lock) already make up just over 80% of sales, so they are class A. The classes use the cumulative share before each product, so the product that crosses the 80% line still counts as A.

Choosing the right denominator

A short checklist you can apply to any share measure:

  • Ask "share of what?" in words first: the company, the current selection, the category, the year.
  • Match the word to the function: company is ALL, selection is ALLSELECTED, category is ALLEXCEPT or removing the lower level only.
  • Always test with a slicer applied. A measure that is right with no slicers can be wrong with one.
  • Format as a percentage, and use DIVIDE so empty denominators give blank, not errors.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A slicer shows only the Gear category. Which denominator makes the visible product shares add up to 100%?

    ALLSELECTED keeps slicer and page filters while removing the visual's own filters, so the total is the sum of what the reader selected.
  2. 2. Two products tie for rank 3. With ties set to DENSE, what rank does the next product get?

    DENSE ranking does not skip numbers, so after two products at 3 the next gets 4. SKIP, the default, would give 5.
  3. 3. How is TOPN typically used in a measure?

    TOPN returns a table of the best rows. Passing it to CALCULATE filters the calculation to those rows.
  4. 4. What can happen when you ask TOPN for 5 rows and the fifth and sixth values are equal?

    TOPN includes every row that ties at the cutoff, so the result can be larger than n.
  5. 5. Which pattern builds a Pareto (cumulative percent) measure?

    Summing the sales of all products that sell at least as much as the current one gives its cumulative position in a descending sort.

Summary

  • RANKX ranks rows by an expression; choose the table (ALL vs ALLSELECTED) and the ties mode (SKIP vs DENSE) deliberately.
  • TOPN returns a table of the best rows for use inside CALCULATE; ties can return more than n rows. The Top N filter in the Filters pane is simpler when no DAX is needed.
  • Percent of total depends on the denominator: ALL for the whole, ALLSELECTED for what the reader sees, ALLEXCEPT or lower-level removal for within-group shares.
  • Running totals filter dates up to MAX of the current cell; a Pareto uses the same idea over products sorted by sales.
  • Always test share measures with slicers applied.

Next, we combine these ideas into reusable recipes: Common DAX Patterns.