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 : 

 

 

and i have a index column DAX formula as below to give the order index number  : 

 

Index = RANKX(TableA,CALCULATE(SUM(TableA[Order Qty]),ALLEXCEPT(TableA,TableA[Order Number])),,ASC,Dense)
 
i wanted to give index number to orders based on which order has less total order qty will get index number  1 .Then the second bigger total order qty will get index 2 etc.Normally my formula was working normal , but i noticed that when the order total qty is same between two different order , my formula gives same index number to each them. As you see in my above example order number "350" and order number " 430 " has the same order qty .So my formula gives them same index number as below picture . (i mentioned with yellow in below picture) 
 

 

 

for fix that  situation , i want that , if the order quantities are equal , system will check one filter too , and when there are equal order qty in each order  , system will check order number , which order number is smaller than other one , smaller one's index number will be earlier than other one.Then again will continue to give index number according to total order qty for the other others.Infact system will seperat orders which have equal total order qty.  i want to see situation like below 
 
 
Thanks in advance 
 
 
 

 
 

 

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)

10 Replies

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

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi erhan_79 

    That's not the same thing as you first asked for.  You should always supply a proper representation of your data at the start.  I'll look into the problem.

    Regards

    Phil

    • erhan_79's avatar
      erhan_79
      Post Prodigy
      5 years ago

      You are right PhilipTreacy  ;

       

      i am sorry it was my fault , i forgot to explain all the scenarious  , thank you very much for your kind supports dear .

       

      i will wait for your good news 

       

      thanks 

       

      erhan