Need Help About Index

[erhan_79]

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 

[PhilipTreacy]

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]

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 

[PhilipTreacy]

Hi @erhan_79 

Download this PBIX file with the following data and code

Create a Calculated Column with this code

Ranking = 
VAR MaxOrder = MAX ( 'Table'[Order Number] )
VAR Result =
    RANKX (
        ALL ( 'Table' ),
        'Table'[Order Qty] * MaxOrder + 'Table'[Order Number],,ASC
    )
RETURN
    Result

Regards

Phil

[erhan_79]

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]

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

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]

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

@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 

[ryan_mayu · Accepted solution]

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

perfect job !  @ryan_mayu 

thank you very much ! it is working as i want