Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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:  
TypeAmountYear
A100002019
A90002018
A80002017
B40002019
B30002018
B20002017
   
Desired result: 
TypeAmountMax
A2700027000
B900027000
1 ACCEPTED SOLUTION

Hi @Anonymous :

 

We can create following measurs to meet your requirement:

 

Measure = SUM('Table'[Amount])

 

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

 

 

10.jpg11.jpg

 


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.

View solution in original post

6 REPLIES 6
v-lid-msft
Community Support
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..

 

 
 

Hi @Anonymous :

 

We can create following measurs to meet your requirement:

 

Measure = SUM('Table'[Amount])

 

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

 

 

10.jpg11.jpg

 


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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.