Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
11 months ago
Solved

Max Number per Week

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

WeekCount/TradeResult
29910
29910
29910
29910
29910
29910
29910
29910
29910
291371
291371
291371
291371
291371
291371
291371
291371
29150
29150
29150
29150
30160
30160
30160
30160
30160
30160
30160
301100
301100
301100
30890
30890
30890
301391
301391
301391
301391
30140
30140
30140
30160
30160
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    11 months ago

    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
    )

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    11 months ago

    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
    )

  • Aburar_123's avatar
    Aburar_123
    Solution Supplier
    11 months ago

    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)
     

     

  • sanalytics's avatar
    sanalytics
    Super User
    11 months ago

    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