cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Return largest category based on a sum

Hi there,

 

I am looking for a (hopefully) simple DAX measure to solve the following problem.

 

I have a range consisting of a number of employees, a chosen product, and their savings in each product. In a different table, I need to create a measure that returns the name of the product that contains the largest combined sum of savings. For e.g. Person 1, this would be "Product B", because the 750,000 in Product B exceeds the 500,000 in Product A (had it been opposite, the measure should return "Product A").

 

Sample.png

 

I am working in PowerPivot in Excel 2013 and have previously relied on "FIRSTNONBLANK" since the ranges have contained no dublicate values. However, that solution is no longer sufficient. Any help is greatly appreciated.

 

Thanks!

16 REPLIES 16

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur, quick follow up on this.

 

Is there an easy way to add a condition to the formula that the ranking is conditional on product not being blank?

 

Recently had a case where a product was missing, but since it contained the largest value, a blank was returned.

 

Thanks!

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur, please see below

Phil123456789_0-1604304560766.png

 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Thanks @Ashish_Mathur, exactly what I was looking for

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors