Max & Allexcept combination to find max value by grouping not working, what's wrong?

230 Views Asked by At

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: 1

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: 2

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

0

There are 0 best solutions below