cancel
Showing results for
Did you mean:
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").

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

Accepted Solutions
Super User III

Hi,

These measures work

Total amount = SUM(Data[Amount])

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User III

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
16 REPLIES 16
Super User IV

@Phil123456789 , Based on what I got

Create a rank Measure and filter Rank =1

rank measure =

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

Proud to be a Super User!

Helper III

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?

Super User IV

@Phil123456789 , 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]))
)

Proud to be a Super User!

Helper III

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.

Super User II

take a look at the following solution:

``````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)

Helper III

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)?

Community Support

Hi @Phil123456789,

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"``````

Regards,

Xiaoxin Sheng

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

Hi,

These measures work

Total amount = SUM(Data[Amount])

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper III

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!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### 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