topic Re: Max Total Sales in Matrix Year and countries in DAX Commands and Tips

Max Total Sales in Matrix Year and countries

mlovejoy — Thu, 14 Sep 2023 17:25:02 GMT

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

Re: Max Total Sales in Matrix Year and countries

ERD — Thu, 14 Sep 2023 17:53:33 GMT

mlovejoy , you can try to use conditions:

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

Re: Max Total Sales in Matrix Year and countries

mlovejoy — Thu, 14 Sep 2023 21:33:45 GMT

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

Re: Max Total Sales in Matrix Year and countries

mlovejoy — Thu, 14 Sep 2023 21:35:38 GMT

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

Re: Max Total Sales in Matrix Year and countries

ERD — Fri, 15 Sep 2023 06:26:52 GMT

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

Re: Max Total Sales in Matrix Year and countries

mlovejoy — Fri, 15 Sep 2023 07:19:18 GMT

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

Thanks

Mark