RANKX trun 1 after insert extra column

[Anonymous]

Hi

I am new in PowerBi  and I learn  a lot in the Community Blog

the Rankx work fine if I just put the ItemID , Sum of Qty and Rank using Table visual

Sum of QTY = SUM('PWBI'[QTY]) and rank = RANKX(ALL(PWBI[ItemNumNoZ]),[Sum of QTY],,DESC,Dense)

If I insert a  extra coloumn , it will show all the item and rank will change 1.

What I need to change  the measure?

thank-you

[v-yulgu-msft]

Hi, @Anonymous

If your expected result is as shown from the below image, you need to modify the rank formula as:

rank = RANKX(ALLEXCEPT('Rankx','Rankx'[ItemNumNoZ]),[Sum of QTY],,DESC,Dense)

If your expected result is like below image, the formula should be changed as:

rank3 = RANKX(ALL('Rankx'[ItemNumNoZ]),CALCULATE(SUM('Rankx'[QTY]),ALLEXCEPT('Rankx','Rankx'[ItemNumNoZ])),,DESC,Dense)

If you have any question, please feel free to ask.

Best regards,
Yuliana Gu

[Anonymous]

Hi Gu

I have a erro when I  replace yours

 Rank and Sum of QTY is Measure . Do I need to change to Coloumn instead of Measure ?

Please have a advices

thank-you 

[v-yulgu-msft · Accepted solution]

Hi, @Anonymous

You should replace 'Rankx' with your own table name. So, in your scenario, the formula:

rank = RANKX(ALLEXCEPT('PWBI','PWBI'[ItemNumNoZ]),[Sum of QTY],,DESC,Dense)

Thanks,
Yuliana Gu