The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Code | USer | TM |
1 | Raj | A |
2 | Raghav | B |
Fact Table
Code | Sales | ID |
1 | 300 | 1 |
1 | 200 | 2 |
2 | 400 | 3 |
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
Solved! Go to Solution.
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
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
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
Code | Sales | ID |
1 | 300 | 1 |
1 | 200 | 2 |
2 | 400 | 3 |
3 | 300 | 4 |
User-
Code | USer | TM |
1 | Raj | A |
2 | Raghav | B |
3 | Raj | C |
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.
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:
User | Sales | Rank |
Raj | 800 | 1 |
Raghav | 400 | 2 |
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:
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
Thankyou soo much! It worked 🙂