Max Total Sales in Matrix Year and countries

[mlovejoy]

Dear community,

I'm trying in a Matrix to retrieve the Maximum Total Sales of all the Year by countries. Very simple in theory, but it seems more complex in reality as I need to remove the Year filter to place the measures inside the matrix. But removing the all(Year) also changes the context of my Max formula. I tried turnaround with Supporting tables, but it doesn't seem to work as I loose the Year with my technics.

Any straight forward methods? Faced this problematic more than once :slight_smile:

Thanks for you help!
Mark Lovejoy

  

[ERD]

@mlovejoy , you can try to use conditions: 

IF ( [current value] = max_value, max_value, BLANK() )

[mlovejoy]

@ERD thanks for your replay, however it is not that easy. How do you define crurent value? Current value will always be the Max value in the context of a Matrix. The only way would be to remove the Year context, but that would then calculate the Max value of all the years together...

[ERD · Accepted solution]

max_value =
VAR max_sales = MAXX ( ALL ( 'Table (3)'[country] ), [sales_amt] )
RETURN
    IF ( [sales_amt] = max_sales, [sales_amt], BLANK () )

[mlovejoy]

Works perfectly thank you! I've learn something today :-). Just replaced all(countries) by all(year)

Thanks

Mark

[mlovejoy]

Btw my max value per year is a sum lf Sales.