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

FreemanZ's avatar
FreemanZ
Super 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
    Super 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: