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

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
3 years ago

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'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