topic Re: ranking in DAX Commands and Tips

ranking

daniel_ricardo — Thu, 30 Mar 2023 17:31:08 GMT

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

Re: ranking

FreemanZ — Fri, 31 Mar 2023 06:00:36 GMT

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 )

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

it worked like:

Re: ranking

FreemanZ — Fri, 31 Mar 2023 06:01:43 GMT

or you add a calculated column like:

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

it worked like:

Re: ranking

daniel_ricardo — Fri, 31 Mar 2023 13:58:31 GMT

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.

Re: ranking

FreemanZ — Fri, 31 Mar 2023 14:10:52 GMT

hi daniel_ricardo

try to provide some sample data?

Re: ranking

daniel_ricardo — Mon, 10 Apr 2023 18:54:17 GMT

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