cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Retrieve MAX row of SUMMARIZE table

I have used the SUMMARIZE DAX in a calculated column and the result is somthing like this (within the SUMMARIZE table):

 Branch Revenue 2 33000 1 22500 3 12000

I want to retrieve the branch with the highest Revenue.
E.g. I want the calculated column to say 2 since it has the max revenue.

My DAX looks like this currently:

MaxBranch =
var ID = Table1[ID]
var BRANCHCODE = Table1[BranchCode]
var IDTABLE = CALCULATETABLE(Table1, FILTER(Table1, Table1[ID] = ID))
var SUMTABLE = SUMMARIZE(IDTABLE, Table1[ID], Table1[BranchCode], "Branch", Table1[BranchCode], "Revenue", SUM(Table1[Revenue]))

return
MAXX(SUMTABLE, [Revenue])

In the above, I was only able to retrive the highest revenue. SUMTABLE is a grouping of revenue totals by ID then Branch. When I write MAXX(SUMTABLE, [Branch]) then it picks up branch 3 as that is the highest number value. However I want it to first find the highest revenue value and output the corresponding branch. Thanks!

2 ACCEPTED SOLUTIONS
Super User

@danialsj , Assuming Revenue is a measure like Sum(table[Revenue])

Top 1 Branch Revenue Rank = CALCULATE([Revenue],TOPN(1,all(Table[Branch]),[Revenue],DESC),VALUES(Table[Branch]))

https://youtu.be/QIVEFp-QiOk

Super User

RETURN

MAXX(TOPN(1,SUMTABLE,[Revenue]),[Branch])

3 REPLIES 3
Super User

RETURN

MAXX(TOPN(1,SUMTABLE,[Revenue]),[Branch])

Super User

@danialsj , Assuming Revenue is a measure like Sum(table[Revenue])

Top 1 Branch Revenue Rank = CALCULATE([Revenue],TOPN(1,all(Table[Branch]),[Revenue],DESC),VALUES(Table[Branch]))

https://youtu.be/QIVEFp-QiOk

Frequent Visitor

Thanks! I changed some things around the DAX you sent and it has worked.

MAXX(TOPN(1, SUMTABLE, [Revenue],DESC), [Branch])

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors