Rank (TopN) within Group

Question

I have a table of data, and I want to sort by STATE, and then for each STATE, return the TopN (say Top 3 for example) COMPANY per state.

Using the generic visual filter for Top 3 Company, by Value, will not quite do what I need. Using that will check for all states, get the top 3 companies, and then return those for each state.

I am trying to return the top 3 companies per state.

State   Company Type        Amount
CA      A       Crystal     100
CA      A       Crystal     200
CA      B       Crystal     3
CA      C       Felt        500
CA      D       Felt        300
NV      A       Rock        10
NV      F       Stone       900
NV      Z       Stone       1000
TX      A       Crystal     20
TX      B       Crystal     10
TX      F       Rock        400
TX      F       Rock        30
RI      A       Glass       40
RI      A       Glass       20
RI      A       Glass       2000
RI      C       Stone       300

Expected Output (done in Excel, but it would be a Matrix in PowerBI):

expected output

So far, I have tried this but I'm not quite sure what it's doing. I added to the visual filter, to show values less than 4, but it doesn't return what I'm trying to return. The below is entered as a New Column (not Measure):

State Company Rank = Var thisGroup = DataTable[State]
RETURN
Rankx(
    Filter(
        DataTable,
        DataTable[State] = thisGroup
    ),
    DataTable[Amount],
    ,
    ,
    Dense
)

Note that I have an additional column in there ("type") that I add in "under" the State->Company. I didn't include here as I don't know if that affects any measures/DAX needed.

You can do first: per-state total amount per company. second: rank per state, based on that total. Create a measure for total Amount per company using: "Total Amount = SUM(DataTable[Amount])". In second step: create a Rank measure (NOT a column) by using: "Rank per State = RANKX( FILTER( ALLSELECTED(DataTable[Company]), MAX(DataTable[State]) = MAX(DataTable[State]) ), [Total Amount], , DESC, DENSE )" — colonel
– colonel, Commented Oct 30 at 20:10
@colonel - I added those, and created a measure as you noted. When I add that "Rank per State" filter to the Matrix visual and choose "Show items where value is less than 4", it doesn't seem to filter out correctly. (Also see my EDIT) — BruceWayne
– BruceWayne, Commented Oct 30 at 20:25
the ranking is being performed across ALL states, not within each state, which is why filtering Rank ≤ 3 doesn't behave correctly in the Matrix. try this: Rank per State = VAR CurrentState = MAX ( DataTable[State] ) RETURN RANKX( FILTER( ALL ( DataTable ), DataTable[State] = CurrentState ), [Total Amount], , DESC, DENSE ) — colonel
– colonel, Commented Oct 30 at 20:26
@colonel - Thanks for the thoughts/idea...Hm, within each state I still have multiple companies with the same rank (e.g. 3), but they are indeed different values so shouldn't be a tied/equal rank. — BruceWayne
– BruceWayne, Commented Oct 30 at 20:30

smpa01 · Accepted Answer · 2025-10-30 21:15:29Z

2

You can use a long version like this and utilize the debugger to print the state of the filter table ft1 for each table operations to see what DAX returns

rank_within_partition_long = 
// Get the current State context for the row
VAR state = MAX('DataTable'[State]) 

// Get the total Amount for the current Company in that State
VAR amt = SUM('DataTable'[Amount]) 

// ft1: Creates a distinct list of State-Company pairs for the current State
// Adds a calculated column "_sum" with the current row's amount (amt)
VAR ft1 =
    DISTINCT(
        ADDCOLUMNS(
            SELECTCOLUMNS(
                FILTER(
                    ALL('DataTable'),
                    'DataTable'[State] = state
                ),
                "State", 'DataTable'[State],
                "Company", 'DataTable'[Company]
            ),
            "_sum", [amt]
        )
    )
// ft2: Adds a "_rank" column by ranking companies within the same State
// Then filters to keep only the row where [_sum] matches the current amt
VAR ft2 =
    FILTER(
        ADDCOLUMNS(
            ft1,
            "_rank",
            RANKX(
                FILTER(ft1, EARLIER([State]) = [State]),
                [_sum],
                ,
                ASC,
                Dense
            )
        ),
        [_sum] = amt
    )
// Extract the rank value from ft2
VAR _rank = MAXX(ft2, [_rank]) 
//debugger for printing
// VAR debugger = TOSCV(ft1,-1)
RETURN _rank

Once you understand that, you can shorten it to

rank_within_partition_short = 
VAR CurrentState = MAX('DataTable'[State])
VAR CurrentAmt = SUM('DataTable'[Amount])
RETURN
RANKX(
    ADDCOLUMNS(
        SUMMARIZE(
            FILTER(ALL('DataTable'), 'DataTable'[State] = CurrentState),
            [Company]
        ),
        "TotalAmt", CALCULATE(SUM('DataTable'[Amount]))
    ),
    [TotalAmt],
    CurrentAmt,
    ASC,
    DENSE
)

answered Oct 30 at 21:15

smpa01

4,4013 gold badges20 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BruceWayne Nov 3 at 15:14

Aha! This works (I switched ASC to DESC), meaning it successfully keeps the topN I'm looking for. However, I note that if I add the rank_within_partition_short as a VALUE to the table, the rank showing is incorrect. But the order is correct, so in practice this is what I'm looking for. Curious though why the ranks are technically incorrect. (Also, do you have a link to using a debugger in PowerBI? I searched around but went down too many rabbit holes)

smpa01 Nov 5 at 15:06

There are few different concepts in DAX - measures/ calculated columns/ calculate tables. I am not sure which one you are trying to do. The one I gave you would work as measures in a viz (i.e. dynamic result; multidimensional table) without error. Also, what works as a measure might not work (give the same result) as a calculated column /calculated table (i.e. static result). So depending on what you are trying to do, you need to edit the code based on the desired end goal.

smpa01 Nov 5 at 15:08

As far as debugger is concerned, you can refer to this TOVSV. It is extremely useful and helps in demystifying DAX

ThxAlot · Accepted Answer · 2025-11-02 10:32:29Z

0

Easy enough,

First, create a measure named "SUM Amount",

SUM Amount = SUM( 'DATATABLE'[Amount] )

then,

_Amount =
VAR __r =
    RANK(
        ALLSELECTED( 'DATATABLE'[State], 'DATATABLE'[Company] ),
        ORDERBY( [SUM Amount], DESC, 'DATATABLE'[Company], DESC ),
        PARTITIONBY( 'DATATABLE'[State] )
    )
RETURN
    IF( __r <= 3, [SUM Amount] )

edited Nov 2 at 10:32

answered Nov 2 at 10:17

ThxAlot

1244 bronze badges

Collectives™ on Stack Overflow

Rank (TopN) within Group

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related