Create calculated Rank column in Powerbi

[Anonymous]

Hi all,

I have two tables- one is dimension and another is fact table. I want to calculate rank of users in Dim table based on sales in Fact table. It has to be in a calculated column.

Dim Table

Code	USer	TM
1	Raj	A
2	Raghav	B

Fact Table

Code	Sales	ID
1	300	1
1	200	2
2	400	3

I tried creating a column like 

sales = sum(Fact[Sales])
rank = RANKX(all(Dim),B[Fact])

 But it gave me 1 for all ranks.

Also When I used it in a calculated measure, it gave correct Ranks.

Request you to guide me.

Thanks

Garima Singh

@amitchandak 

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file, whether it suits your requirement.

Rank CC =
VAR _sales =
    SUMX ( RELATEDTABLE ( Sales ), Sales[Sales] )
VAR _summaizetable =
    ADDCOLUMNS ( User, "@Sales", SUMX ( RELATEDTABLE ( Sales ), Sales[Sales] ) )
VAR _ranking =
    COUNTROWS ( FILTER ( _summaizetable, [@Sales] >= _sales ) )
RETURN
    _ranking

[Anonymous]

It works only when there is single record for a user in sales table. But in case there is multiple records then same user is repeated again and ranking is different.

Table: Sales

Code	Sales	ID
1	300	1
1	200	2
2	400	3
3	300	4

User-

Code	USer	TM
1	Raj	A
2	Raghav	B
3	Raj	C

[FreemanZ]

hi @Anonymous 

try to add a column like this:

SalesRank = 
RANKX(
    ALL(User[Code]), 
    CALCULATE(SUM(Sales[Sales]), ALLEXCEPT(User, User[Code]))
)

it worked like this:

p.s. please consider @someone,  if you seek further suggestions.

[Anonymous]

Hi,

Thanks for the response. This is something we were able to achieve. But we want data to be reflected with distinct user and there rank at overall level. Not two rows for a user.

Something like:

User	Sales	Rank
Raj	800	1
Raghav	400	2

[FreemanZ]

hi @Anonymous 

just noticed your reply.  You may create a column like this:

SalesRank = 
RANKX(
    ALL(User[USer]), 
    CALCULATE(SUM(Sales[Sales]), ALLEXCEPT(User, User[USer]))
)

it worked like this:

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try:

Sales = 
VAR _codes = CALCULATETABLE(VALUES('User'[Code]),FILTER(ALL('User'),'User'[USer]=EARLIER('User'[USer])))
VAR _sales = CALCULATE(SUM('Sales'[Sales]),FILTER(ALL('Sales'),'Sales'[Code] IN _codes))
RETURN
_sales

Ranking = RANKX(ALL('User'),'User'[Sales],,DESC,Dense)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous]

Thankyou soo much! It worked 🙂