ranking

[daniel_ricardo]

I need to rank sellers based on a field that is not being filtered in the visual, just by the team that the seller belongs. It's a report made to the seller, so he must see his position among sellers on the same team. Each seller can have more than one account, that's my lowest level of glanularity.

I was able to solve the seller position in all teams, but not in his own team.

Here is the last dax i tried:

CALCULATE(
    RANKX(
        ALL(account[seller]),
        [sales_amount]
        ,,, Dense
    ),
    KEEPFILTERS(fact[store_id]),
    ALLSELECTED(account[team], account[cluster])
)

[FreemanZ]

or you add a calculated column like:

RankingColumn = 
RANKX(
    FILTER(
        TableName,
        TableName[Team] = EARLIER(TableName[Team])
    ),
    TableName[Sales],
    ,ASC
)

it worked like:

[FreemanZ]

hi @daniel_ricardo 

not sure if i fully get you. 

supposing you have table like this:

Team	Seller	Sales
A	A1	1
A	A2	2
B	B1	1
B	B2	2
B	B3	3

try to plot a measure with columns [team] and [seller] and a measure like:

RankingMeasure = 
RANKX(
    FILTER(
        ALL(TableName),
        TableName[Team] = MAX(TableName[Team])
    ),
    CALCULATE(SUM(TableName[Sales])),
    ,ASC
)

or 

RankingMeasure2 = 
RANKX(
    CALCULATETABLE(
        TableName,
        ALLEXCEPT(TableName, TableName[Team])
    ),
    CALCULATE(SUM(TableName[Sales])),
    ,ASC
)

it worked like:

[daniel_ricardo]

Yes, your suggestion works in a table, but i'm using that measure in a card. In table, i think in some way we have a implicity filter behavior, because the team column is there (sorry if I'm saying something wrong). In my report I have a filter by seller, and the team is not filtered. I tested here and in this scenario it didn't work.

[FreemanZ]

hi @daniel_ricardo 

try to provide some sample data?

[daniel_ricardo]

04-03-2023 10:29 AM

Sorry, I can´t share because some data privacy, but if it helps here is the data model with relevant fields to my expression. We need to get the ranking position by des_gestor, segmento and dim_agencia.porte_agencia.

chave_ua_cart is the lowest level
des_gestor (seller) can be present in more than one chave_ua_cart
des_gestor can work in more than one segmento but must be part of one single dim_agencia.porte_agencia