Need assistance finding Highest value of the same category

[NTRAN]

I have noticed that this similar topic has been asked and resolved several times by other users here, but their solution does not work for me. 

So I'm trying to find the highest value that of the same category and display that value in a new column. I've tried several solutions but nothing works so far.  A picture would be the best way to explain it.  

Highest Metric Same Order = CALCULATE(MAX('Dummy Data'[Metric A]),ALLEXCEPT('Dummy Data', 'Dummy Data'[Order]))  

It is strange because the filter seems to function correctly (the right column all return the same values in the category) but the value it pulls out seems bizarre and doesn't follow any logic.  

I've also tried this formula for the column, which returns the same values:  

Highest Metric Same Order = CALCULATE(MAX('Dummy Data'[Metric A]),FILTER('Dummy Data','Dummy Data'[Order] == EARLIER('Dummy Data'[Order])))

The pseudocode is simple:  

- Filter table for rows with the same (Order).  

- Get the maximum value of (Metric A) in that filtered table  

- Display that maximum value in the new column   

Yet the results don't make any sense, so I'm in a bit of a bind here.  

Any help would be greatly appreciated.  

[NTRAN]

Ok, so the problem just kind of... sorted itself out.  

I was using an iferror() function in my dummy data table and return a string if the conditions that create the metric are not met.  

However, it seems like that power is confused when a column contains both string and integer value and that kinda mess up the max() function. 

Even with those string data point filtered out, it still affects the max() function somehow, which is my theory.  

However, once i just left those cells (blank), it now works fine.    

Still, I appreciate you guys trying to help me out.  

[dax]

Hi NTRAN, 

According to your description, it seems that this is caused by the data type, so you could try to change the data type to int to see whether this work or not. 

By  the way, if you solved this problem, you could mark corresponding post as answer or share your solutions. That way, people who in this forum and have similar issue will benefit from it.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdbuchanan71 · Accepted solution]

Ahh, it was doing the MAX() on the alphabetical rather than numeric value.  6 is higher alphabetically than 1000000 (Just like F is higher than AZZZZZZZ) so that is what was causing the result.

[Anonymous]

Hi @NTRAN 

try with this one:

Measure = CALCULATE(MAX(('Dummy Data'[Metric A]),GROUPBY('Dummy Data', 'Dummy Data'[Order]),ALL())

[Nathaniel_C]

Highest Metric Same Order = CALCULATE(MAX(DummyData[Metric A]),ALLEXCEPT(DummyData,DummyData[Order]))

Hi @NTRAN ,

This works for me. 
dummydata is my pbix.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[jdbuchanan71]

@NTRAN 

Can you share your sample data?  When I try to recreate the problem I get the correct results:

Highest Metric Same Order = 
CALCULATE(
    MAX('Dummy Data'[Metric A]),
    ALLEXCEPT('Dummy Data','Dummy Data'[Order])
)