Forum Discussion
Solved
Help on MAX formula
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!
I have updated the formula in the same comment. Please double check and let me know what results you get?
