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.
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
)