Skip to main content
cancel
Showing results for 
Search instead 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

Reply
danialsj
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):

 

BranchRevenue
233000
122500
312000

 

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
amitchandak
Super User
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

View solution in original post

wdx223_Daniel
Super User
Super User

RETURN

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

View solution in original post

3 REPLIES 3
wdx223_Daniel
Super User
Super User

RETURN

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

amitchandak
Super User
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

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

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

Helpful resources

Announcements
Europe Fabric Conference

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.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

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

August Carousel

Fabric Community Update - August 2024

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