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

[DanSanDST]

Grettings, using this image as an example:

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?

[v-menakakota]

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 

[v-menakakota]

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.

[v-menakakota]

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]

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

[Ashish_Mathur]

Hi,

These measures work

V = SUM(Data[value])

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

Hope this helps.

[MarkLaf]

Providing DAX and Power Query approaches.

Here is a DAX approach keeping your original table.

Table

id	value	location
1	10	B
1	1	B
1	2	C
1	5	D
2	3	A
2	1	C
2	1	C

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

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

[rohit1991]

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.

[DanSanDST]

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

[v-menakakota]

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]

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?

[DanSanDST]

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