CODEWITHSUNDEEP
databaseexcel-formulapowerbi
Cameron Stubber
Cameron Stubber

Reputation: 311

Return only single max value row in Power BI Desktop

I have the following table of Parts which are sold for a particular job, which is the Order Number.

I am trying to extract just the Description of the most expensive part so that I can put it onto a single value card.

I have tried for a day mucking around with CALCULATE, MAX, TOP, SELECTEDVALUE, I cant seem to figure it out. I'm sure it is something simple too...

Would appreciate it if somebody can help me retrieve it in a way that I can see what I missed and learn for future.

My page is filtered by DrillThrough on the Order Number which filters the parts list for me.

enter image description here

Essentially, I just want the card to show 'PUMP,DTH,ELE'. My approach was to just select the top 1 row when the parts list is sorted descending by Amount in LC but it so far has not been as simple as that :(

Should it be a calculated column or measure on my Order table which has that string?

Upvotes: 0

Views: 9828

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

You should be able to create a measure that does this and then place that measure on a card.

Most Expensive Part = LOOKUPVALUE(Parts[Description],Parts[Amount],MAX(Parts[Amount]))

The MAX(Parts[Amount] piece gives you the maximal amount. Then you look up the description corresponding to that amount.

Upvotes: 2

Related Questions