Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 :
Solved! Go to Solution.
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)
Proud to be a Super User!
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
Proud to be a Super User!
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
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
Proud to be a Super User!
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
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)
Proud to be a Super User!
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
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?
Proud to be a Super User!
@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
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)
Proud to be a Super User!
User | Count |
---|---|
86 | |
82 | |
68 | |
64 | |
55 |
User | Count |
---|---|
120 | |
99 | |
91 | |
83 | |
65 |