cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
efanta
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
KHorseman
Community Champion
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!




View solution in original post

11 REPLIES 11
Greg_Deckler
Super User IV
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!




KHorseman
Community Champion
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!




efanta
Frequent Visitor

@KHorseman should consider only once the max value.

KHorseman
Community Champion
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!




@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
Sean
Community Champion
Community Champion

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

 

However No Excuses for @KHorseman Smiley LOL

 

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

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

 

 

Konstantinos Ioannou

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? Smiley Happy





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

Proud to be a Super User!




KHorseman
Community Champion
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!




View solution in original post

Really helpful @KHorseman - thanks!

efanta
Frequent Visitor

It works! Thanks!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors