Rank Table A according to Table B based on Decreasing Column

[iamprajot]

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

[v-jiascu-msft]

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

[iamprajot]

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.

[v-jiascu-msft]

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

[iamprajot]

Hi,

Rank Column is not actually there, it was a Result View that I showed.

I am afraid I cannot share the actual data, I hope you would understand but I assure you that the two sample tables I showed are exactly identical to what I have here.

Now I have

Table A which have two Columns Supplier ID & Unit Price &

Table B which have Column Supplier ID

Now what I wanted was to Rank Unit Prices according to the Supplier ID weather it is in Table A or Table B.

If I do not Rank it according to Supplier ID then the Unit Price will go on like 1, 2, 3 ......1000 and what I want is that for Supplier ID 1 Ranks should be 1,2,3 and then for Next Supplier ID 2 Rank should be like 1,2,3,4 (Limited to how many times Unit Prices are for a particular Supplier ID)

I think I have explained myself better now and

I am not sure I am going in the right direction or gonna hit a wall again but what I actually wanted was to get 3 Maximum prices for every supplier, that's why I needed to rank them so that I could pick first 3.

Thanks

[v-jiascu-msft · Accepted solution]

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

[iamprajot]

Remarkable !

Thanks