Find duplicate column using max value fom another column and create a new column coded as 0 or 1

[Anonymous]

Hi,
Need to create a new column which finds the duplicates and gives a one to those duplicates which has the highest value in a different column. If 2 duplicates have the highest value give 1 only to the 1st. In the below table for the same customer and the report month if there is a duplicate value the one with the highest sales will be coded as 1 in the Selected column, for eg the combination of SWA and 31 Dec 21 has 2 sales rows; one with 2 and another with 4. The one with 4 will be coded as 1 in the Selected column and the other will be coded as 0.
|cust|Report_month|Sales|Selected|
|SWA| 31-Aug-21 | 0 | 1 |
|SWA| 30-Sep-21 | 0 | 1 |
|SWA| 31-Oct-21 | 0 | 1 |
**|SWA|30-Nov-21 | 0 | 1 |**
**|SWA|31-Dec-21 | 2 | 0 |**
|SWA|31-Dec-21 | 4 | 1 |
|CWH|31-May-21 | 0 | 1 |
**|CWH|31-Aug-21 | 1 | 1 |**
**|CWH|31-Aug-21 | 0 | 0 |**
|CWH|31-Oct-21 | 0 | 1 |
|CWH|30-Nov-21 | 0 | 1 |
|CWH|31-Dec-21 | 0 | 1 |
|OSS|31-Aug-21 | 2 | 1 |
**|OSS|31-Dec-21 | 0 | 0 |**
|OSS|30-Sep-21 | 0 | 1 |
**|OSS|31-Oct-21 | 0 | 0 |**
**|OSS|31-Oct-21 | 5 | 1 |**
|OSS|30-Nov-21 | 0 | 1 |
|OSS|31-Aug-21 | 0 | 0 |
**|OSS|31-Dec-21 | 4 | 1 |**

[Anonymous · Accepted solution]

Hi @Anonymous ,

Create a column as below.

Column = 
var _max = CALCULATE(MAX('Table'[Sales]),FILTER('Table','Table'[cust]=EARLIER('Table'[cust])&&'Table'[Report_month]=EARLIER('Table'[Report_month])))
return
IF('Table'[Sales]=_max,1,0)

Best Regards,

Jay

[Whitewater100]

Hello:

Please check this out. If this analysis were to go into multiple years we would neeed to change month column to Year&Month, for now it is just answering the question as presented. Hope it helps.

https://drive.google.com/file/d/1vXfrY_ha5Sik7idM_F3hAs9EVSHiStiA/view?usp=sharing 

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddCxDoIwEAbgV2mYbdJWfQCig4uSeAMDIYbUiw4oRIqJSR9exAYoPYabvvx3fy7LIt02xp6xrl7m8qie5m6hKLGxgCVqg9coX2URpLFla8nj9saVZJaJbiSbmOCA9YJJnmhDWhc7VW+aJN+j/pPqmaTNmNqlhx8di0+w0NFQXy6TGG85osr3RJd3qaHhhBIA75YKyEuJCdH/dSmvoaBpO0vR5ecNw4XB5/Mv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sales", Int64.Type}, {"Selected", Int64.Type}, {"Report_month", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (k)=> if Table.Sort(Table.SelectRows(#"Changed Type",each k[cust]=[cust] and k[Report_month]=[Report_month]),{{"Sales", Order.Descending}})[Sales]{0} = k[Sales] then 1 else 0)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".