Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to Solution.
I have updated the formula in the same comment. Please double check and let me know what results you get?
Hi @YC
Please try
Max price (per material) =
CALCULATE (
MAX ( 'Dataset'[Price] ),
ALLEXCEPT ( 'Dataset', 'Dataset'[Material], 'Dataset'[Rgion] )
)
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.
I have updated the formula in the same comment. Please double check and let me know what results you get?
With second field changed to region worked perfectly fine. Thank you very much for the help!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |