Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Create calculated Rank column in Powerbi

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 Ta...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
Anonymous
Not applicable
3 years ago

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

CodeSalesID
13001
12002
24003
33004

 

User-

CodeUSerTM
1RajA
2RaghavB
3RajC
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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:

     

    UserSalesRank

    Raj

    		8001
    Raghav4002
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        Thankyou soo much! It worked 🙂

    • FreemanZ's avatar
      FreemanZ
      Icon for Super User rankSuper User
      3 years ago

      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: