Forum Discussion

itchyeyeballs's avatar
itchyeyeballs
Impactful Individual
9 years ago
Solved

SumX using max

Hi all, 

 

I'm having an issue with using SumX and can't figure out what I'm doing wrong (the 'X' functions always trip me up)

 

I'm trying to calculate the sum of the maximum values accross groups.

 

My formula is 

=if(HASONEVALUE(Table1[col2]),
	max(Table1[Value]),
	sumx(values(Table1[col2]),max(table1[value]))
)

The results look like

 

The third grand total is showing 30 rather than 16, it seems like my sumx is using 15 twice rather than 15 + 1. Any ideas where I'm going wrong?

 

Thanks 

 

 

Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    itchyeyeballs

     

    hi, just add a Calculate in Max.

     

    Measure 2 =
IF (
    HASONEVALUE ( Table1[col2] ),
    MAX ( Table1[Value] ),
    SUMX ( VALUES ( Table1[Col2] ), CALCULATE ( MAX ( table1[value] ) ) )
)

3 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    itchyeyeballs

     

    hi, just add a Calculate in Max.

     

    Measure 2 =
IF (
    HASONEVALUE ( Table1[col2] ),
    MAX ( Table1[Value] ),
    SUMX ( VALUES ( Table1[Col2] ), CALCULATE ( MAX ( table1[value] ) ) )
)
      • OwenAuger's avatar
        OwenAuger
        Super User
        9 years ago

        A side note:

        You can also get rid of the HASONEVALUE test, since if Col2 has one value then SUMX will iterate over that one value anyway:

         

        =
SUMX ( VALUES ( Table1[Col2] ), CALCULATE ( MAX ( table1[value] ) ) )