Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
Proud to be a Super User!
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
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.
@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?
Proud to be a Super User!
Sum of Max = SUMX( VALUES(TableName[City]), MAX(TableName[Sales]))
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 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
@Sean @KHorseman Mine is European 11:30pm - so luckily I must dreamed the formula.
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?
Proud to be a Super User!
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.
Proud to be a Super User!
It works! Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |