PlantOps BI

Top N and Others

A chart of 80 customers is unreadable, and a chart of the top 10 leaves out half the revenue. The answer is the top 10 plus one "Others" bar that holds the rest, so the bars still add up to the total. A visual-level Top N filter drops the rest instead of grouping it; this DAX keeps it as one bar.

2025 revenue on the demo orders, top 10 customers by code and everyone else.
  1. C0017 Retail $9.31M
  2. C0036 Distributor $7.87M
  3. C0064 Retail $4.78M
  4. C0067 Distributor $4.59M
  5. C0073 Distributor $3.55M
  6. C0054 Distributor $2.36M
  7. C0074 OEM $2.33M
  8. C0062 Distributor $2.30M
  9. C0050 Distributor $2.10M
  10. C0011 Retail $1.64M
  11. Others (70) $39.24M

How Concentrated Is It?

The top 10 customers carry 51.0% of revenue and the largest alone 11.6%. A single number for the whole spread is the Herfindahl-Hirschman index: square each customer's percentage share and add them up. It runs from near 0 (many small customers) to 10,000 (one customer). Here it is 410, the same concentration as 24 equally sized customers. Watch the trend rather than the level: an index that climbs means a few accounts are coming to matter more.

The DAX

The axis is a small calculated table that is not related to anything, so the measure decides what each row shows. The top customers come from TOPN over the customers the slicers leave (ALLSELECTED), and Others is everything else. Because both use the same customer list, the bars always sum to the total, which the grand total row shows directly.

-- A disconnected axis: every customer code plus one "Others" row
Customer Axis =
UNION (
    SELECTCOLUMNS ( dim_customer, "Customer", dim_customer[customer_code] ),
    ROW ( "Customer", "Others" )
)
Revenue = SUMX ( fact_orders, fact_orders[qty] * fact_orders[unit_price] )

-- Put 'Customer Axis'[Customer] on the rows of a bar chart or table
Revenue Top N and Others =
VAR N = 10
VAR Customers = ALLSELECTED ( dim_customer[customer_code] )
-- TOPN keeps every customer tied at the cut, so ties can show more than N rows
VAR Top = TOPN ( N, Customers, [Revenue] )
VAR Axis = SELECTEDVALUE ( 'Customer Axis'[Customer] )
RETURN
    SWITCH (
        TRUE (),
        NOT ISINSCOPE ( 'Customer Axis'[Customer] ), CALCULATE ( [Revenue], Customers ),
        Axis = "Others", CALCULATE ( [Revenue], EXCEPT ( Customers, Top ) ),
        Axis IN Top, CALCULATE ( [Revenue], dim_customer[customer_code] = Axis )
    )

-- Sort field: largest first, Others always last. On a bar chart, add it
-- to Tooltips so it can be chosen as the sort.
Top N Sort =
IF (
    SELECTEDVALUE ( 'Customer Axis'[Customer] ) = "Others",
    -1,
    [Revenue Top N and Others]
)

-- Herfindahl-Hirschman index: sum of squared percentage shares, 0 to 10,000
-- Shares of the revenue in the current filter context, so a card gives the
-- company HHI and a row per segment gives each segment's own
Customer HHI =
VAR Total = [Revenue]
RETURN
    SUMX (
        VALUES ( dim_customer[customer_code] ),
        ( DIVIDE ( [Revenue], Total ) * 100 ) ^ 2
    )

Make N a what-if parameter to let readers pick it. The site's tests check that the top 10 and Others add up to the total, that a tie at the cut keeps every tied customer the way TOPN does, and the HHI on two known cases. Customers are shown by code; their names in the demo data are made up.

Price-volume-mix · ABC classification · About the demo dataset