I'm quite new to Power BI and DAX but I have what I think should be a pretty simple application of the combination of Max and Allexcept to find the max value of one column, based on the value of another column.
I have one table called EvergradeGroups that contains the group that a product belongs to. Then a Product table which contains the product information for each product, it contains a column called DurabilityPoints which gives a score from 1-10. I want to figure out what the max DurabilityPoints score is for each EvergradeGroup.
Here is the formula I am currently using.
DurabilityMax =
CALCULATE (
MAX ( cr481_product[DurabilityPoints] ),
ALLEXCEPT ( cr481_evergradegroups, cr481_evergradegroups[EvergradeGroup] )
)
As you can see something is wrong because the first Evergrade group "Blankets & Throws" got a max of 16.5 when the scores only go up to 10. Screenshot of results:

I have also tried moving the Evergrade groupings into the Product table so that all the information is in the same table. I used a calculated column and the Lookupvalue formula to grab the evergrade groups for each product. However when I then used only product table columns using the formula below, the max scores got even wackier!
DurabilityMax =
CALCULATE (
MAX ( cr481_product[DurabilityPoints] ),
ALLEXCEPT ( cr481_product, cr481_product[EvergradeGroup] )
)
Here's the results:

What could I be doing wrong? All the other results seem fine using the first formula, except for that one "Blankets & Throws" group.