Forum Discussion
Rank Table A according to Table B based on Decreasing Column
I want to Rank Table A according to Table B based on Decreasing Unit Price.
Something like this.
| Table A | Table B | ||||
| SupplierID | UnitPrice | Rank | SupplierID | City | |
| 1 | 81 | 1 | 1 | London | |
| 1 | 18 | 2 | 2 | New Orleans | |
| 1 | 10 | 3 | 3 | Ann Arbor | |
| 2 | 19 | 1 | |||
| 2 | 12 | 2 | |||
| 3 | 16 | 1 | |||
| 3 | 10 | 2 |
Hi iamprajot,
The other columns affect the result. Try this formula please. I added another column in the sample.
Measure (or Calculated column) = CALCULATE ( RANKX ( ALL ( 'TableA'[UnitPrice] ), CALCULATE ( SUM ( 'TableA'[UnitPrice] ) ) ), ALLEXCEPT ( TableA, TableA[SupplierID], TableA[UnitPrice] ) )Best Regards!
Dale
6 Replies
Hi iamprajot,
Actually, the rank has nothing with table B, table A has everything. Please try this formula. It works as both a calculated column and a measure. ( The column "rank" could be an example. It should be deleted.)
Column / Measure = RANKX ( ALL ( 'TableA'[UnitPrice] ), CALCULATE ( SUM ( 'TableA'[UnitPrice] ) ) )
Best Regards!
Dale
Hi,
Thanks for sparing time.
Result in screenshot seems good but I tried both Column/Measure but it is giving me 1 in the whole column.
Also I don't see the use of Supplier ID of Table A to categorise/group the Unit Price in the Query.
I am still curious how did that query worked for you.
Hi iamprajot,
Actually grouping the Unit Price is the work of the function "RANKX". Please reference: https://msdn.microsoft.com/en-us/library/gg492185.aspx.
If you use the formula as a Calculated Column, you need to delete the column "Rank", which I think is a description. If you use it as a measure, you need to add column "Supplier ID" and "Unit Price" in the report, which is the context.
Maybe your table have other columns. Could you please post a complete sample and the snapshots of your results?
Best Regards!
Dale
