Ranking a column from highest to lowest

[RingoSun]

So I have this BrandDim table and I want to rank them from highest to lowest.

So the value that I'll be using to rank them will be based on the quantity column of this SOS table 

And also do take note of the relationship of the tables

With all that said, I want a new column to be added to my BrandDim table and it would contain the ranking numbers. So whichever brand has the highest quantity will have 1 as its value in that new column, the second highest will have 2 and so on....

How do I achieve this?

PBIX FILE

[Querynalyst]

@kring,

You can first create a new table:

newBrandTable = SUMMARIZE(BrandDim, BrandDim[Brand], BrandDim[BrandId], "qty", SUM(SOS[Quantity]))

Then simply create a new column "rank"

rank = RANK.EQ(newBrand[qty], newBrand[qty])

Hope this can help

[amitchandak]

@RingoSun , If you need a column rank

Create a new column

Qty = Sumx(Relatedtable(SOS ), [Qty])

Rank = Rankx(BrandDim ,[Qty],,desc,dense)

Or measure based on qty

Rankx(allselected(BrandDim[Brand]), calculate(Sum(SOS[Qty])) ,, desc, dense)

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

[RingoSun]

@amitchandak 

Hi! the first option worked but I want to take this a step further. In my Brand column I want to exclude the Client row from the ranking and just put its default value as 1 or perhaps 0 in the Ranking column. The rest will be ranked from highest to lowest normally. Is that possible?

[amitchandak · Accepted solution]

@RingoSun , Try

Rank = if([Brand] <> "Client" , Rankx(Filter(BrandDim, [Brand] <> "Client") ,[Qty],,desc,dense), blank())