Forum Discussion

Wiene24's avatar
Wiene24
Helper II
7 years ago

Rankx

Hi,

 

I have a little problem with my RANKX.

 

Two tables are involved: Costumor and Order

 

I have a measure to calculate the total amount of the orders for each costumor:

Omzet = SUM('dba Dossier'[DBCalcTotaalVerkoopTransport])
 
Now if I want to rank all the costumors, they all give me value 1.
 
I have this measure to calculate the rank:
Omzetplaats = RANKX(ALL('dba Relatie'[DebiteurNummer]);[Omzet])
 
 
DebiteurNummer and RelatieTelefoon are columns in the dba relatie table.
 
When I use calculated columns it works, but I don't want calculated columns because then I can't work with my slicers.
 
Later I want to do more ranks so I can get an average rank, but I only want to use the top 100 costumors based on the total sales.
 
Hope someone can help me, I've been trying to sort it out for a while now but nothing seems to work with measures.
 
Kind regards,
 
Tim Wijnen
Need Help

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Wiene24 ,

     

    Try this mesaure:

    Omzetplaats = 

                         RANKX(

                                     ALL('dba Relatie'[DebiteurNummer]),

                                       [OMZET],

                                        ,ASC

                                      )

     

     

    Thanks,

    Tejaswi

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    HI, Wiene24 

    Just try this formula:

    Omzetplaats 2 = RANKX(ALL('dba Relatie'),[OMZET] )

    or

    Omzetplaats 3 = RANKX(ALLSELECTED('dba Relatie'),[OMZET] )

    Result:

    For your formula, if you just drag [DebiteurNummer] into visual, it will work too.

     

    Best Regards,

    Lin

    • Wiene24's avatar
      Wiene24
      Helper II
      7 years ago

      It's getting closer, but not working completely yet!

       

       

      I'm using this formula now: 

      Omzetplaats = RANKX(ALL('dba Relatie');[Omzet];;DESC)
       
      I want the rank to change if I change my slicers, that's why I didn't use ALLSELECTED.
       
      But no idea why it leaves gaps now and why some are ranked as equals.
       
      Kind regards,
       
      Tim Wijnen

       

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        7 years ago

        hi, Wiene24 

        If you want the rank to change if you change my slicers, please use ALLSELECTED instead of ALL.

        don't use Omzetplaats = RANKX(ALL('dba Relatie');[Omzet];;DESC)

         

        If you still have problem, please share a simple sample pbix file.

         

        Best Regards,

        Lin