Forum Discussion

Nethergate's avatar
Nethergate
New Member
4 years ago
Solved

Max by category

Hi guys,   I have the data set below which has duplicated Cost center headcount records due to multiple Account Type.   Can you advise which Dax formula I can use to get to the desired outcome co...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Nethergate , You need to add an index column in the power query and then try this DAX new column

     


    var _min = minx(filter(Table, [Country] =earlier([Country]) && [Cost Center] =earlier([cost Center]) && [Month] =earlier([Month])), [Index])
    var _max = maxx(filter(Table, [Country] =earlier([Country]) && [Cost Center] =earlier([cost Center]) && [Month] =earlier([Month])), [headcount])
    return
    if([Index] =_min, _max, blank())

Nethergate's avatar
Nethergate
New Member
4 years ago

It's a column

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Nethergate , You need to add an index column in the power query and then try this DAX new column

     


    var _min = minx(filter(Table, [Country] =earlier([Country]) && [Cost Center] =earlier([cost Center]) && [Month] =earlier([Month])), [Index])
    var _max = maxx(filter(Table, [Country] =earlier([Country]) && [Cost Center] =earlier([cost Center]) && [Month] =earlier([Month])), [headcount])
    return
    if([Index] =_min, _max, blank())