Max value by different categories in column and showing project number that matches that value

[jmdaily83]

I have a table like the below

And I would like the end result to show as below with the corresponding project name listed for the max value of that attribute.   When I do it in a table visual and use Max on the value it shows properly, but when I add the project number it shows all of the projects and not the corresponding project to the max value. 

Intended Power 

[jmdaily83]

@Anonymous ; @Ashish_Mathur ; @rajendraongole1 

Thank you all for helping.  However I have tried those calculations and they're not working. I will try to explain the issue I'm running into better.

Below I have the attributes and the Max value for that attribute.  There is only 1 row for each attribute which is correct and this filters properly based on related project information in another table. For example, the max Internal Engineering value is $281,295.

However, the client also wants the project listed that is associated with that max amount. When I add the project number it now shows the max for the attribute & each project.  I only want the project showing that is associated to the $281,295 value. I can't do this is the table by calculated column since I need it to be dynamic and based on filters.

Here is some of the data - hopefully it can be copy/pasted easily

Attribute	Max	Project Number
Intended Power (MW)	180	10049100
Intended Power (MW)	50	10056000
Intended Power (MW)	4	10064800
Intended Power (MW)	10	Pharr and Crosby BESS
Intended Energy (MWh)	180	10049100
Intended Energy (MWh)	50	10056000
Intended Energy (MWh)	4	10064800
Intended Energy (MWh)	10	Pharr and Crosby BESS
System Time (hours)	1	10049100
System Time (hours)	1	10056000
System Time (hours)	1	10064800
System Time (hours)	1	Pharr and Crosby BESS
Internal Engineering $	236199.8	10049100
Internal Engineering $	279190	10056000
Internal Engineering $	281295	10064800
External Engineering $	811379.9	10049100
kWh DC BOL	242816	10049100
kWh DC BOL	76024	10056000
kWh DC BOL	7360	10064800
kWh DC BOL	13416	Pharr and Crosby BESS
Engineering	3.65	10049100
Engineering	3.633589	10056000
Engineering	38.21943	10064800
Engineering	11.03882	Pharr and Crosby BESS

[Ashish_Mathur · Accepted solution]

Hi,

These measures work

M = max(Data[Max])

Measure = CALCULATE(MIN(Data[Project Number]),FILTER(VALUES(Data[Max]),Data[Max]=max(Data[Max])))

Hope this helps.

[jmdaily83]

This worked! Thank you so much

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.

[Anonymous]

Hi @jmdaily83 ,

I create a table as you mentioned.

Then I create a measure and here is the DAX code.

MaxProject = 
CALCULATE ( MAX ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Project] ) )

I think you can use a Slicer and a card.

Best Regards

Yilong Zhou

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

[rajendraongole1]

Hi @jmdaily83 - Project name alongside the maximum value for each attribute.I have created a calculated column 

Create a new calculated column:

MaxValuePerProject =

VAR MaxValue = CALCULATE(MAX('Table'[Value]), ALLEXCEPT('Table', 'Table'[Attribute]))

RETURN

    IF('Table'[Value] = MaxValue, 'Table'[BD Opportunity], BLANK())

Snap:

Snap2

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[jmdaily83]

@rajendraongole1 Thank you - that works if I don't filter the table down but there are other project variables (in a related table) that will be sliced on and the results should be dynamic. I should have explained this in my initial post.  For example. If the project is in California it would show the max values by attribute and the project with that max value.

Thank you for your help