Help on MAX formula

[YC]

Hello! I am trying to create a measure or a column to calculate the max value by material and by region, as per below example. 

Basically, if I will not filter anything on region, I want to see Max by material only, if I filter region I want to see max by material & region.

Region	Material	Price	Max price (per material/per region)	Max price (per material)
Europe	material A	3	3	5
NA	material A	5	5	5
LA	material A	2	2	5
Europe	material A	1	3	5
Europe	material B	9	45	98
Europe	material B	45	45	98
Asia	material B	2	2	98
NA	material B	98	98	98
NA	material B	13	98	98

I managed to calculate it separately (by material, and separate by region), using this formula:

CALCULATE(MAX('Dataset'[Price]), FILTER(ALLSELECTED('Dataset'),'Dataset'[Material]=EARLIER('Dataset'[Material])))

But I need to have it done based on both criteria so that I can show the correct value when filtering a certain region. 

Can someone please guide me on what is the correct formula to obtain this?

Thank you!

[tamerj1]

Hi @YC 
Please try 

Max price (per material) =
CALCULATE (
    MAX ( 'Dataset'[Price] ),
    ALLEXCEPT ( 'Dataset', 'Dataset'[Material], 'Dataset'[Rgion] )
)

[YC]

Hello! Thank you very much for the formula. This is working to show max per material indeed, but if I would filter a certain region, will not give the correct result.

[tamerj1 · Accepted solution]

I have updated the formula in the same comment. Please double check and let me know what results you get?

[YC]

With second field changed to region worked perfectly fine. Thank you very much for the help!!