Measure to compute the ranking

[Anonymous]

Hi Experts

I'm trying use the below formula to get the ranking based on the qty. But not able to achieve the result as it is ending up in error.

Ranking = rankx(all('table1'[Account],'Mat'[Qty]),,Desc,DESC)

The 'Mat'[Qty] is not seen from the suggested list. The Qty is a column in the Mat table but we do have a measure called "Qty_Measure"(which is sum(Mat[Qty]). Neither "Qty_Measure" is seen to include in the formula.

Can someone please suggest.

Thanks
Mahad

[amitchandak · Accepted solution]

@Anonymous , You can create a column rank, new column

Ranking = rankx('table1'[Account],'Mat'[Qty],,Desc,DESC)

when you want to measure rank you need a measure in expression

Ranking = rankx(all('table1'[Account],calculate(sum('Mat'[Qty])),,Desc,DESC)

[Anonymous]

Hi Amitchandak

Thanks for your reply.

like I mentioned above. When I try to type the formula on the measure "Ranking", it is not suggesting me the "Qty_Measure" (which is a measure). In another word I cannot see "Qy_Measure" to complete the formula you have given.

Please suggest.

Thanks
Mahad

[v-jingzhang]

Hi @Anonymous 

Amit's measure should work. I have created a sample with it. You can download the attachment to see details. Note that there is a relationship between two tables based on Account column. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.