Forum Discussion
Solved
Creating a Rank for each Item By Custumer
Hi! tnx a lot for reading my post, My goal is to obtain the row TOP in powerr bi using rankx, This is a rank for each item by customer in the same table....
aquanr18 Please try this as a "New Column"
Top = RANKX(FILTER(Test58Rank,Test58Rank[Client]=EARLIER(Test58Rank[Client])),Test58Rank[Sales],Test58Rank[Sales],DESC)
Here you can see the table I'm using
https://drive.google.com/open?id=1VNrH9QlZ6BKpP4NbC08qrmYJbVCi_o17
aquanr18 Please try this as a "New Column"
Top = RANKX(FILTER(Test58Rank,Test58Rank[Client]=EARLIER(Test58Rank[Client])),Test58Rank[Sales],Test58Rank[Sales],DESC)
Thanks ! this is exactly what I wanted, may you explain how does the formula work?
aquanr18 That's cool !!
Just to make it simple - If you are from SQL background, It is just similar to RANK() OVER() along with PARTITION BY.
So here we want to rank for each group (in this case client) that's why we have nested the FILTER particular client records (each group) and then assigning a rank. To identify current row group, EARLIER function came into picture. That is it.... May be official Microsoft Documentation will explain it better