Forum Discussion

daniel_ricardo's avatar
daniel_ricardo
Frequent Visitor
3 years ago

ranking

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...
FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi daniel_ricardo 

not sure if i fully get you. 

supposing you have table like this:

TeamSellerSales
AA11
AA22
BB11
BB22
BB33

 

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's avatar
    daniel_ricardo
    Frequent Visitor
    3 years ago

    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.

      • daniel_ricardo's avatar
        daniel_ricardo
        Frequent Visitor
        3 years ago
        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