Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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!

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

These measures work

Total amount = SUM(Data[Amount])

Top product = FIRSTNONBLANK ( TOPN ( 1, VALUES ( Data[Product] ), [Total amount] ), 1 )

Hope this helps.

Untitled.png


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

View solution in original post

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

20 REPLIES 20
Ashish_Mathur
Super User
Super User

Hi,

These measures work

Total amount = SUM(Data[Amount])

Top product = FIRSTNONBLANK ( TOPN ( 1, VALUES ( Data[Product] ), [Total amount] ), 1 )

Hope this helps.

Untitled.png


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

Apologies for my delayed response, did not have a chance to try and implement the suggestions.

 

@Ashish_Mathur, you solution worked perfectly, thanks. And likewise, thanks to everyone else who came with suggestions, very appreciated!

You are welcome.


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

@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/
Anonymous
Not applicable

@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/
Anonymous
Not applicable

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/

Hi Ashish,

I have stumbled across this thread and it seems to be very similar to what I am trying to achieve, but I am unable to get the results with your measures.

 

Scenario:

  • I have 4 qualified Project managers working on a single project, and each person is required to do different hours of work.
  • Of the 4, I need to choose one project manager for this single project.
  • The rule to determine the project manager is to SUM(Required Effort), and the project manager with the highest totals hours will be assigned the role.
  • I need this person to be displayed across all rows for the same projectID (see screenshot of expected results).
  • Also, I need for the Lead Project Manager to always be attached to the unique ProjectID, if I need to use it in another table where I only want to display the ProjectID alongside the Lead Project Manager.

deevo_0-1666350123623.png

deevo_1-1666350353913.png

 

I would appreciate your help on this one, I am so close yet so far.

Regards

Denny

 

 

Hi,

These calculated column formulas work

Required effort per Manager for a ProjectID = CALCULATE(SUM(Data[Required Effort]),FILTER(Data,Data[ProjectID]=EARLIER(Data[ProjectID])&&Data[Project Manager]=EARLIER(Data[Project Manager])))
Max of accumulated required effort = CALCULATE(MAX(Data[Required effort per Manager for a ProjectID]),FILTER(Data,Data[ProjectID]=EARLIER(Data[ProjectID])))
Lead project manager = LOOKUPVALUE(Data[Project Manager],Data[Required effort per Manager for a ProjectID],Data[Max of accumulated required effort],Data[ProjectID],Data[ProjectID])

Hope this helps.

Untitled.png


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

This worked perfectly. Thank you so much. Your knowledge is amazing. 

I will have to go back and break each formula down to understand how it works.

You are welcome.  Thank you for your kind words.  If my previous reply helped, please mark that reply as Answer.


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

Hi @Anonymous 

take a look at the following solution:

 

24-09-_2020_19-48-41.png

 

 

Max Amount = 
Var _Rank = RANKX(ALL('Table'[Product]),[Sum of Amount],,DESC)
RETURN
    IF(_Rank = 1, [Sum of Amount], BLANK())

 

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Anonymous
Not applicable

Thanks @FrankAT, worked a charm in PowerBI.

 

However, when trying to convert the formula to Excel PowerPivot, I cannot seem to make it work.

 

I believe the problem is that Excel will not let you return a text string in a pivot table (unless it is used with e.g. FIRSTNONBLANK).

 

How do I adjust the formula, so I get the formula to return the correct Product as a text string (I do not need to show the actual amounts in the table, just the product)?

Hi @Anonymous,

Excel and power bi use different logic and structure to store data tables.

Perhaps you can try to convert your table to the query table and use the 'M query' to create a reference query to summarize raw table records.

let
    Source = RawTable,//change to your query table name
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Product", type text}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name", "Product"}, {{"Total", each List.Sum([Amount]), type nullable number}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Name"}, {{"Count", each Table.LastN(_, 1), type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows1", "Count", {"Product", "Total"}, {"Product", "Total"})
in
    #"Expanded Count"

1.png
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

@Anonymous , Based on what I got

 

Create a rank Measure and filter Rank =1

rank measure =

Rankx(all(Table[product]), calculate(Sum(Table[Amount])))

 

 

Anonymous
Not applicable

Thanks @amitchandak, formula seems to work.

 

However, because I use the names ("Person 1", "Person 2", etc.) as the row filter in the Pivot Table, only one product is currently returned as value. So, the rank function simply returns "1" for all employees, and hence it is not possible to filter.

 

Any alternative suggestions?

 

 

@Anonymous , this how-to measure Rank work. The one I suggested will create a single rank if used with the product and if used with the employee you will get inside the employee. 

 

Try like 

RANKX (
FILTER(
ALL( /////////Or allselected 
'Table'[employee],
'Table'[product ]
),
'Table'[employee] = MAX('Table'[employee])
),
CALCULATE(SUM('Table'[Amount]))
)

 

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

Anonymous
Not applicable

Thanks @amitchandak, I will have a look at the links.

 

Not sure how the proposed measure works, since it requires taking MAX of employee, which is a string.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.