Forum Discussion

AndrewPF's avatar
AndrewPF
Icon for Helper V rankHelper V
4 years ago
Solved

return a single value based on maximum value of another column

I have a simple two-column table, similar in layout to this: 

GroupTotal
Red504
Green714
Yellow923
Blue258
White432
Black805
Orange601
Purple80

from which I need to find the maximum value of Total, and the Group to which it belongs. 

I use the MAX function to find the maximum Total, but how can I get the Group associated with that maximum? 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi AndrewPF 

     

    Something like this? Is it possible you have more than 1 Group for the max Total?

    test = CALCULATE(VALUES('Table'[Group]),'Table'[Total]=MAX('Table'[Total]))

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi AndrewPF 

     

    Something like this? Is it possible you have more than 1 Group for the max Total?

    test = CALCULATE(VALUES('Table'[Group]),'Table'[Total]=MAX('Table'[Total]))

     

    • AndrewPF's avatar
      AndrewPF
      Icon for Helper V rankHelper V
      4 years ago

      I used it, and it worked, thanks. 

      But then I tried it with MIN: 

      test = CALCULATE(VALUES('Table'[Group]),'Table'[Total]=MIN('Table'[Total]))

      and it did not return the correct result. 

      There is no duplication between the numbers. 

      Any ideas? 

      • AndrewPF's avatar
        AndrewPF
        Icon for Helper V rankHelper V
        4 years ago

        Ignore last message.  

        It works with MIN too. 

        Thank you