Max Number per Week

[AllanBerces]

Hi pls need help on how can i have the result i required, on my table from week 1 to current on the result i want to registered 1 if the number is max and 0 if not on the particular week. sample below for week 29 and 30.

OUTPUT

Week	Count/Trade	Result
29	91	0
29	91	0
29	91	0
29	91	0
29	91	0
29	91	0
29	91	0
29	91	0
29	91	0
29	137	1
29	137	1
29	137	1
29	137	1
29	137	1
29	137	1
29	137	1
29	137	1
29	15	0
29	15	0
29	15	0
29	15	0
30	16	0
30	16	0
30	16	0
30	16	0
30	16	0
30	16	0
30	16	0
30	110	0
30	110	0
30	110	0
30	89	0
30	89	0
30	89	0
30	139	1
30	139	1
30	139	1
30	139	1
30	14	0
30	14	0
30	14	0
30	16	0
30	16	0

[sanalytics]

@AllanBerces 
I would recommend to create custom column rather than calculated column. Below is the M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrJU0lGyNFSK1aET29DYnD4cU6LZxgYgthkN2IYGxHAsLAmzDY3J4JgQwUZzeiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week = _t, Trade = _t]),
    TypeChanged = Table.TransformColumnTypes(Source,{{"Week", Int64.Type}, {"Trade", Int64.Type}}),
    Result = let
GroupTable =  
Table.Group( 
TypeChanged, {"Week"},{{"AllRows",each _}}
)[AllRows],
MaxTradeAdded = 
List.Transform(GroupTable,(x) =>
Table.AddColumn(x,"Result", each if [Trade] =List.Max(x[Trade]) then 1 else 0 )
),
Result = Table.Combine(MaxTradeAdded)
in
Result
in
    Result

Hopt this will help

Regards,

sanalytics

[Aburar_123]

Hi @AllanBerces ,

Please try this calculated column

Flag =

var Max_value = CALCULATE(MAX('Table'[Count/Trade]),FILTER('Table','Table'[Week]=EARLIER('Table'[Week])))

return if(Max_value='Table'[Count/Trade],1,0)

[bhanu_gautam · Accepted solution]

@AllanBerces 

You will need to create a new calculated column in your data model to determine if a value is the maximum for its respective week.

Result =
IF(
'YourTable'[Count/Trade] =
CALCULATE(
MAX('YourTable'[Count/Trade]),
ALLEXCEPT('YourTable', 'YourTable'[Week])
),
1,
0
)

[AllanBerces]

Hi @bhanu_gautam thank you very much work as i need