cancel
Showing results for
Did you mean:
Frequent Visitor

## sum of max values

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!

1 ACCEPTED SOLUTION
Community Champion

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

11 REPLIES 11
Super User IV

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.

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Community Champion

@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?

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Frequent Visitor

@KHorseman should consider only once the max value.

Community Champion

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Memorable Member

@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

Konstantinos Ioannou
Community Champion

@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

Memorable Member

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

Konstantinos Ioannou
Community Champion

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?

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Community Champion

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Frequent Visitor

Really helpful @KHorseman - thanks!

Frequent Visitor

It works! Thanks!

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors