sum of max values

[efanta]

Hi everyone,

I'm trying to sum the max values of some items from a table. I have generated an example table for explanation purposes:

City                              Sales           

Buenos Aires               1.000

Buenos Aires               1.000

Mendoza                     2.000

Mendoza                     500

Córdoba                      3.000

The idea is to sum only the max values for each City (For Buenos Aires = 1.000 ; For Mendoza = 2.000; For Córdoba = 3.000). Therefore, the total sum should be 6.000.- Please note that for Buenos Aires the value is the same for those rows, but the idea is only consider the max. Although, the minimum value for Córdoba (500) it should not be considered in the sum.

Is there any expression for this?

Thanks!

[Danny2020]

Hello,

What would be the DAX in a scenario with a second city table from which a city filter selection is added? With the formula provided the total is for all the cities, even if a single city is filtered. Thanks

[Greg_Deckler]

Some different ways to achieve this depending on the visualization you wish to display. Easiest way is to create a measure like so:

MaxSum = MAX([Sales])

Then just put this measure in a matrix visualization along with City and you will get the sum.

[KHorseman]

@efanta what should happen if the same max value shows up more than once? Like

City                              Sales           

Buenos Aires               1.000

Buenos Aires               1.000

Mendoza                     2.000

Mendoza                     2.000

Mendoza                     500

Córdoba                      3.000

Should the total still be 6000 or would it now be 8000?

[efanta]

@KHorseman should consider only once the max value.

[KHorseman]

Sum of Max = SUMX( VALUES(TableName[City]), MAX(TableName[Sales]))

[konstantinos]

@KHorseman Is late that is why you probably missed it..Wrap MAX() with calculate

Sum of Max =
SUMX ( VALUES ( TableName[City] ); CALCULATE ( MAX ( TableName[Sales] ) ) )

On the Total ( no city filters) it sums 3 times the MAX of all table 3x3000

[Sean]

@konstantinos I was just looking at this too and missed it

However No Excuses for @KHorseman

Plus its not really late I think he is in the Eastern Time zone so around 5:30pm

I'm in the Central so 4:30pm here

[konstantinos]

@Sean @KHorseman Mine is European 11:30pm - so luckily I must dreamed the formula.

[KHorseman]

D'oh! Nesting a CALCULATE inside a SUMX looks so weird to me that I never remember when it's needed. Good catch @konstantinos

@Sean now, what's the speed performance on the two methods?

[KHorseman · Accepted solution]

Alternative Sum of Max = SUMX(
SUMMARIZE(TableName, TableName[City], "Max Sales", MAX(TableName[Sales])),

[Max Sales])

My first formula works on a test table I already had, but it doesn't seem to work right when I typed in your sample table. I can't figure out why that's the case because they both follow a similar pattern, but anyway this second formula works for both.

[Debbs1976]

Really helpful @KHorseman - thanks!

[efanta]

It works! Thanks!