Returning the Max of a measure

[Anonymous]

Hi,

I have a summary table which is grouped by "Type", there are x number of years for each type but the table sums each years to return one value for each Type.  I then need to return the MAX value overall but I can only seem to return the Max value year of each Type instead of one overall highest number.

Example of the Table and desired result is below - for Type B I need to return the Max overall, not Type B Max:  

Any help greatly appreciated, thanks!

Table:
Type	Amount	Year
A	10000	2019
A	9000	2018
A	8000	2017
B	4000	2019
B	3000	2018
B	2000	2017
Desired result:
Type	Amount	Max
A	27000	27000
B	9000	27000

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[Anonymous]

@Anonymous 

sum by type

Measure = CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Type]))

then to get max

Measure 2 = MAXX(ALL('Table'),[Measure])

[Anonymous]

Hi - thanks I tried this and it works but I have realised there is a rogue amount in a year 2020.  I have a filter on all pages excluding year 2020, and have changed your solution to say AllExcept YOA but the rogue amount still comes through.  Any ideas as to how to modify your solution to allow the measure to read the filter and ignore 2020 year please?

[Anonymous]

I want to avoid deleting rows in the data source by the way

[Anonymous]

I might have been over-complicating this.  I simply need to return the higherst value in the below measure (but the measures change depending on filters selected:

Measure

4838

4831

3540

So I need a measure which returns 4838 for all rows in this example..

[v-lid-msft · Accepted solution]

Hi @Anonymous :

We can create following measurs to meet your requirement:

Measure = SUM('Table'[Amount])

Measure 2 = MAXX(ALLSELECTED('Table'[Type]),[Measure])

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

Best regards,