Forum Discussion
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!
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.
12 Replies
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?
Anonymous should consider only once the max value.
- Greg_Deckler
Community Champion
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.
Danny2020Helper I
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
