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

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 🙂