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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
DanSanDST
Frequent Visitor

Getting value from a column based on max of a grouped list

Grettings, using this image as an example:

DanSanDST_0-1751654367174.png

 

I duplicated my original table, used keep duplicated to get only repeated IDs, grouped it to sum the values for each ID, so i can later join both tables (original one with duplicated removed), and keep as the VALUE my new value column that is the sum of them.

 

Now the only thing left is to get the LOCATION from the row that has the higher VALUE, it doesnt seem to be something that hard to do, but i havent been able to do it.

 

This seems like the usual LOOKUPVALUE, but in many cases people suggest to do grouping then join tables, wich i am already doing for this.

 

Any suggestions?

11 REPLIES 11
v-menakakota
Community Support
Community Support

Hi  @DanSanDST  ,

Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank @danextian   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 
I hope the details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

Best Regards, 
Community Support Team 

Hi @DanSanDST ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

Hi @DanSanDST ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

danextian
Super User
Super User

Hi @DanSanDST 

 

Try this calculated table. mydata refers to the origina table name.

SummarizedTable = 
-- Step 1: Create a summarized table with total value and max value per id
VAR _MaxValue =
    SUMMARIZECOLUMNS (
        mydata[id],
        "@value", CALCULATE ( SUM ( mydata[value] ) ),        -- Total value per id
        "@Maxvalue", CALCULATE ( MAX ( mydata[value] ) )      -- Maximum single value per id
    )

-- Step 2: Add the corresponding location where the max value occurred
VAR _Location =
    ADDCOLUMNS (
        _MaxValue,
        "@location",
            VAR CurrentID = [id]                              -- Current id in iteration
            VAR CurrentMaxValue = [@Maxvalue]                 -- Max value for the current id
            RETURN
                MAXX (
                    FILTER (
                        mydata,
                        mydata[id] = CurrentID &&
                        mydata[value] = CurrentMaxValue       -- Filter rows that match both id and max value
                    ),
                    [location]                                 -- Return the location for that row
                )
    )

-- Step 3: Select final columns to return
VAR _SelectColumns =
    SELECTCOLUMNS (
        _Location,
        "id", [id],
        "value", [@value],            -- Total value
        "location", [@location]       -- Location where the max value occurred
    )

-- Return the final summarized table
RETURN
    _SelectColumns

danextian_0-1751771307668.png

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Ashish_Mathur
Super User
Super User

Hi,

These measures work

V = SUM(Data[value])
Measure = CONCATENATEX(TOPN(1,VALUES(Data[Location]),[v]),Data[Location],", ")

Hope this helps.

Ashish_Mathur_0-1751771155186.png

 


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

Providing DAX and Power Query approaches.

 

Here is a DAX approach keeping your original table.

 

Table

idvaluelocation
110B
11B
12C
15D
23A
21C
21C

 

You can then do the following in a table visual to get the values you want:

 

id: add Table[id] to field welll and set to "do not summarize"

 

value: you can just add Table[value] to the field well and use implicit SUM. You can rename "Sum of value" to "value"

 

top location: drop in the following measure

 

Top Location = 
CALCULATE( 
    SELECTEDVALUE( 'Table'[location] ), 
    INDEX( 1, ORDERBY( 'Table'[value], DESC ), PARTITIONBY( 'Table'[id] ) ) 
)

 

MarkLaf_0-1751727490738.png

 

If going Power Query route, if I'm understanding you correctly, you can get the right table with a single Group step.

 

let
    Source = Table,
    GroupOnId = Table.Group(
        Source,
        {"id"},
        {
            {"value", each List.Sum([value]), type number},
            {"location", each Table.First(Table.Sort(_, {{"value", Order.Descending}}))[location], type text}
        }
    )
in
    GroupOnId

 

MarkLaf_1-1751728283696.png

 

rohit1991
Super User
Super User

Hi @DanSanDST ,

You're almost there with your approach! Since you already have a grouped table where you're summing the values by id, the next step is to retrieve the location from the row with the maximum value per group (per id) in your original table.

Step-by-Step Solution in Power Query

  1. Start with your original table.
    (Let’s call it OriginalTable)

  2. Group the table by id, and create an "All Rows" column:

    GroupedTable = Table.Group(OriginalTable, {"id"}, {
        {"AllData", each _, type table [id=number, value=number, location=text]}
    })
    
  3. Within each group, find the row with the max value:

    Add a custom column to extract the row with the max value:

    AddMaxRow = Table.AddColumn(GroupedTable, "MaxRow", each
        Table.Max([AllData], "value")
    )
    
  4. Extract only the fields you need (id, value, location):
    FinalResult = Table.SelectColumns(AddMaxRow, {"id", "MaxRow"})
    FinalExpanded = Table.ExpandRecordColumn(FinalResult, "MaxRow", {"value", "location"})
    ​

     

    Notes:
  • If you already have the summed values by id, but just need the location of the max row, you can still use the original table to find the row with max value per group.

  • This approach is more scalable and doesn't require merging or LOOKUPVALUE logic.


Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

Thanks, this solution was simple and worked for me. I forgot to mention i was working with Power Query.

Hi @DanSanDST ,

We really appreciate your efforts and for letting us know the update on the issue.

Please continue using fabric community forum for your further assistance.

 

If this is the solution that has worked for you please accept the reply as solution so as to help other community members who may face similar issue in the future.

Thank you.

 

danextian
Super User
Super User

Hi @DanSanDST 

What is your expected result? What is your "the row that has the higher VALUE"? What if you have three or more ids?





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

I have many ids with differenr values. Like the image shows, i need to have only 1 row per id, but show the sum of all their values(column), and in location since you cant sum that text data, show the location from the row with the highest value column per id

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.