cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Returning the Max of a measure

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
1 ACCEPTED SOLUTION
Community Support

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,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Community Support

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,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@Anonymous

sum by type

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

then to get max
Measure 2 = MAXX(ALL('Table'),[Measure])
Anonymous
Not applicable

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
Not applicable

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

Anonymous
Not applicable

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..

Community Support

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,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.