Forum Discussion

erhan_79's avatar
erhan_79
Post Prodigy
5 years ago
Solved

Need Help About Index

Hi there ;   I need your support about to fix my below index  formula , let me explain what the problem :    I have a table which includes "order number" and  "order qty ",as  below :    ...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    erhan_79 

    please try this

    rank = 
 RANKX('Table',CALCULATE(SUM('Table'[Order Qty]),ALLEXCEPT('Table','Table'[Order Number])),,ASC,Dense)
+RANKX('Table',VALUE('Table'[Order Number]),,ASC,Dense)/10


rank2 = RANKX('Table',VALUE('Table'[rank]),,ASC,Dense)

erhan_79's avatar
erhan_79
Post Prodigy
5 years ago

Dear PhilipTreacy  ;

 

thanks for your reply but sometimes orders can have different order quantities at the same time .Your formula for this situation gives error for index.You can see below , just i added some line for each orders , when the order numbers repeats index is not working properly , it is indexing all the lines , it is not grouping by order number

 

 

For this situation , i expcect like that : 

Could you pls check it for me 

 

 

thanks for your kşnd supports

 

 

 

ryan_mayu's avatar
ryan_mayu
Super User
5 years ago

erhan_79 

here is a workaround for you

Rank = 
RANKX(all('Table'),VALUE('Table'[Order Qty]),,ASC)+RANKX(all('Table'),VALUE('Table'[Order Number]),,ASC)/10

rank2 = RANKX('Table',VALUE('Table'[Rank]),,ASC)

  • erhan_79's avatar
    erhan_79
    Post Prodigy
    5 years ago

    Dear ryan_mayu ;

     

    thank you but , i want a index as below as i mentioned my previous message , your last one is not giving my request , or am i wrong ? 

     

    on your examples i can not see as my below examples 

     

     

     

     

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      5 years ago

      erhan_79 

      i sorted by index. To double check with your logic, sort by order qty lowest to highest, then order number lowest to highest. Is that correct?

       

      • erhan_79's avatar
        erhan_79
        Post Prodigy
        5 years ago

        ryan_mayu  but i need a new column which index numbers will be as i mentioned , i need the index numbers as i mentioned below picture , lets repeat rules to you too ;

         

        First index number  "1" will be given the order which has less total order quantity .

        then which order total quantity will be bigger it will get index number "2 " 

        there can be 2 or maybe 3 line for one order . ın our example all orders has 2 lines , For that situtaions if an order got an index number all line of order will be  same . 

         

        in our below example "order 350 "and "order 430 " , has equal order quantities , so for this situations system will give priorty for the order which one has smaller order number .

         

        on your examples all lines has different index numbers dear , i just want a index number distrubution as below