locked
DAX formula sum of max RRS feed

  • Question

  • I am looking for a Dax measure that calculates this:

    There are three columns with values (columns a, b, c).

    I need SUM( MAX (each row ) )

    For the example hereunder:

    15 + 15 + 16 + 16 + 8 = 70


    Any ideas?

    Thanks, Wannes.

    Friday, May 4, 2018 6:08 PM

Answers

  • I don't think there is any variant of max which works across a row, but you could do this with a SUMX and SWITCH

    eg

    Measure = SUMX(Table1,
        SWITCH( true(), 
        Table1[a] >= Table1[b] && Table1[a] >= Table1[c],Table1[a],
        Table1[b] >= Table1[a] && Table1[b] >= Table1[c], Table1[b],
        Table1[c]))


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by pildo Sunday, May 6, 2018 2:00 PM
    • Marked as answer by wannes1964 Monday, May 7, 2018 6:17 PM
    Sunday, May 6, 2018 3:20 AM
  • Hi Darren,

    thank you for this solution, it works correct.

    I also figured out this one:

    Measure = SUMX( Table1; MAX( MAX( Table1[a]; Table1[b] ) ; Table1[c] ) )

    This gives the same result.

    I dont know witch one is most performant.

    KR

    Wannes

    Sunday, May 6, 2018 8:04 AM
  • Hi wannes1964,

    Thanks for your question.

    I am glad to know your issue has been resolved. Please kindly mark the helpful replies as answers. By doing so, It will benefit all community members who are facing this similar issues. Your contribution is highly appreciated.

    As I test, both of the solutions provided by your and Darren worked as expected.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by wannes1964 Monday, May 7, 2018 6:18 PM
    Monday, May 7, 2018 2:20 AM

All replies

  • Hi Wannes,

    Please try below:

    DAXMeassure = SUMX(TableName, TableName[column a] +TableName[column b] +TableName[column c])

    Thanks,

    Alambir

    Please vote this as helpful or Mark this as answered if this helps.


    Thanks, Alambir Please vote this as helpful or Mark as answered if this helps!


    Friday, May 4, 2018 6:36 PM
  • Hi,

    thanks for the quick answer, but I an not looking for the sum of the columns, but for the sum of the max for each row:

    max(row1) + max(row2) + max(row3)... etc

    KR

    Wannes

    Friday, May 4, 2018 6:54 PM
  • Hi Wannes,

    In that case, Have you tried below:

    DAXMeassure = SUMX(TableName, CALCULATE(MAX(TableName[column a])) + CALCULATE(MAX(TableName[column b])) + CALCULATE(MAX(TableName[column c])))

    Thanks,

    Alambir

    Please vote this as helpful or Mark this as answered if this helps.


    Thanks, Alambir Please vote this as helpful or Mark as answered if this helps!

    Friday, May 4, 2018 7:19 PM
  • Excel 2010/2013/2016 Power Query (aka Get & Transform)
    PQ acts as a front end for PP.
    http://www.mediafire.com/file/741bcibicd58mcg/05_04_18a.xlsx

    Saturday, May 5, 2018 10:53 PM
  • I don't think there is any variant of max which works across a row, but you could do this with a SUMX and SWITCH

    eg

    Measure = SUMX(Table1,
        SWITCH( true(), 
        Table1[a] >= Table1[b] && Table1[a] >= Table1[c],Table1[a],
        Table1[b] >= Table1[a] && Table1[b] >= Table1[c], Table1[b],
        Table1[c]))


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by pildo Sunday, May 6, 2018 2:00 PM
    • Marked as answer by wannes1964 Monday, May 7, 2018 6:17 PM
    Sunday, May 6, 2018 3:20 AM
  • Hi Darren,

    thank you for this solution, it works correct.

    I also figured out this one:

    Measure = SUMX( Table1; MAX( MAX( Table1[a]; Table1[b] ) ; Table1[c] ) )

    This gives the same result.

    I dont know witch one is most performant.

    KR

    Wannes

    Sunday, May 6, 2018 8:04 AM
  • I also figured out this one:

    Measure = SUMX( Table1; MAX( MAX( Table1[a]; Table1[b] ) ; Table1[c] ) )

    This gives the same result.

    I dont know witch one is most performant.

    I think your version might be slightly faster as there should be a few less comparisons, it's probably easier to understand what the code is doing. I have not actually used MAX with that particular overload, so thanks for sharing.


    http://darren.gosbell.com - please mark correct answers

    Sunday, May 6, 2018 10:27 PM
  • Hi wannes1964,

    Thanks for your question.

    I am glad to know your issue has been resolved. Please kindly mark the helpful replies as answers. By doing so, It will benefit all community members who are facing this similar issues. Your contribution is highly appreciated.

    As I test, both of the solutions provided by your and Darren worked as expected.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by wannes1964 Monday, May 7, 2018 6:18 PM
    Monday, May 7, 2018 2:20 AM