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 Table

 

CodeUSerTM
1Raj

A

2RaghavB

 

Fact Table

 

CodeSalesID
1300

1

12002
24003

 

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 

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

7 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super 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
        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.