cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MNGoodyear Frequent Visitor
Frequent Visitor

Rank using multiple values

I am using rank and have come across a problem.

 

I want to rank by current month qty, which is fine, however where multiple customers have the same month qty they both get assigned the same rank number, as shown below

 

I want to rank by current month, and if 2 customers have the same sales qty in a month, then rank by the one with the highest 2018 sales qty , as shown by the example below.

 

is this possible?

 

Thanks

 

Customer2018 SalesCurrent MonthRank by Current Month Rank by Current Month, By 2018 Sales
a10005004 5
b20001005 8
15008002 3
d5005004 7
e80106 9
f30007003 4
g120010001 2
h500010001 1
i7005004 6

 

1 ACCEPTED SOLUTION

Accepted Solutions
Iamnvt Member
Member

Re: Rank using multiple values

you can use this calculated column:

Rank by Current Month, By 2018 Sales = RANKX(Table1, Table1[Current Month]+Table1[2018 Sales]/1000000, ,DESC,Dense)

here is the PBI file

https://1drv.ms/u/s!Aps8poidQa5zk6pOehW7GfqpkL-PUQ

View solution in original post

2 REPLIES 2
Iamnvt Member
Member

Re: Rank using multiple values

you can use this calculated column:

Rank by Current Month, By 2018 Sales = RANKX(Table1, Table1[Current Month]+Table1[2018 Sales]/1000000, ,DESC,Dense)

here is the PBI file

https://1drv.ms/u/s!Aps8poidQa5zk6pOehW7GfqpkL-PUQ

View solution in original post

Highlighted
Super User
Super User

Re: Rank using multiple values

@MNGoodyear  In Power Query Editor, just with few clicks based on the sample data provided

 

1. Sort the CurrentMonth in descending

2. Sort the 2018Sales in descending

3. Add Index column starts with 1

 

Here is the M-Code for the same

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY07DsAwCEOvUjFnIKS/u6AMTf/3v0CJUaNKXQx6GFuVFgoUmdnGAO0pB6VimziOfgReuwrcOEMFfGvv35AdLkSYjGCHbcmDJ2gCPqtL3j4M8Msjf/xu/60wPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, #"2018 Sales" = _t, #"Current Month" = _t, #"Rank by Current Month" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"2018 Sales", Int64.Type}, {"Current Month", Int64.Type}, {"Rank by Current Month", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Current Month", Order.Descending}, {"2018 Sales", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1)
in
    #"Added Index"

image.png

 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,344)