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 belo...
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
    )

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