Rankx with the column that is sorted by other column in the data table

[Young_G_Han]

Hello.

I have a problem with the rankx formula.

Code     Name

1           Bread

2           Onion

3           Cake

4           Pizza

I have a list of products and the related code. I want to sort the name of the product by the code, not by alphabetic order.

So I arrange the column with the code.

When I make a ranking column with the rankx, it shows only 1 for the ranking as below.

Ranking   Name    Value

1              Pizza      10,000

1              Onion     8,000

1              Bread      5,000

1              Cake       3,000

What I want is.... when I sort the table by Value.

Ranking   Name    Value

1              Pizza      10,000

2              Onion     8,000

3              Bread      5,000

4              Cake       3,000

When I sort the table by Name.

Ranking   Name    Value

3              Bread      5,000

2              Onion     8,000

4              Cake       3,000

1              Pizza      10,000

I used the following formula. If I am wrong, please let me have the correct formula.

Calculate(rankx(product[Name], [Value], ,Desc), allselected(product[Code])

[Ashish_Mathur]

Hi,

Try this measure

Amount = sum(Data[Value])

Rank = rankx(all(Data[Name]),[Amount])

Hope this helps.

[dhruvinushah · Accepted solution]

Hi, @Young_G_Han  Try this: 

FoodRank = 
RANKX (
    ALLSELECTED ( 'TableTest'[Name], 'TableTest'[Order] ),
    CALCULATE ( SUM ( 'TableTest'[Value] ) ),
    ,
    DESC
)

Hope this helps. 

[Strive]

Hello I have the query on the same lines

In the data table have column below column

ID          Application      Component Activity

In the Activity Lookup table has below columns (contains list of activities with Order of activity column which assigns the order to each activity based on actual sequence not alphbatically)

Activity Name     Order of Activity

Activity Name in look up table is in 1:* relation ship with Activity column in data table

Created a matrix in power bi where

Rows = Components

Columns = Activyt Name column from the lookup table (note the column is sorted by order of activity)

Now trying to format/rank the  columns in the matrix visual so that based on ranking column will have alternate color.  Below is the code for the same. However ranking does not follow the sorting order instead ranks based on alphabatical sequence of the column headers and in the matrix column headers are sorted based activity order column . 

As a effect columns headers ordered by the activity order but the ranking is based on the alphabatical order becasue of which coloring is based on ranking while column header sorted by activity order taking away effect of alternate coloring

Below is the code

CF-Activity =

VAR SelectedActivity = SELECTEDVALUE('lk Activity name Alias'[Level6])

VAR RankTable = ALLSELECTED('lk Activity name Alias'[Level6])

VAR ActivityRank =

RANKX(

        RankTable,

        'lk Activity name Alias'[Level6],

        SelectedActivity,

        ASC,Dense

)

 RETURN ActivityRank

//VAR CF = IF(ISEVEN(ActivityRank), "#E6F7FF", "#D3D3D3") RETURN CF

[Young_G_Han]

Great Thanks it is working!!