How to total a unique ID's highest valued items within a unique quality

[Tsunoda_2211]

Hi, there!

First-time poster in this Community, so feel free to let me know if I'm violating any best practices for future reference!

I have a data set where each row has a unique Child ID and these Child IDs are grouped into Parent IDs. Each Child ID is categorized into a Unit, and Parent IDs may have Child IDs of various Units or only 1 Unit. See below simplified version of my data set:

Parent ID	Child ID	Unit	Value
101	2000	A	2
101	2001	A	4
101	2002	B	3
102	2003	B	8
102	2004	B	12
103	2005	A	2
103	2006	B	5
103	2007	C	6
103	2008	C	7

My goal is to find a Total Parent ID Value which is the sum of a Parent ID's highest value Child IDs of each unique Unit with that same Parent ID. I've struggled to word that coherently, but perhaps the goal results will help demonstrate:

Parent ID	Total Parent ID Value
101	7
102	12
103	14

Ex: to walkthrough Parent ID 103's calculation, it has 4 Child IDs that fall into 3 unique Units. The highest value Child ID of those 3 Units (2, 5, and 7) summed together make 14. Parent ID 102 is even more simple in that it only has 1 unique Unit represented, so the Total Parent ID Value is equal to the Child ID with the greatest value.

How could I most efficiently find the Total Parent ID Value given the data set? I've been able to write a measure that finds a specific Parent ID's highest value Child ID for a given Unit (see below), but I do not know how to then find the sum all of a given Parent ID's highest value Child IDs for each unique Unit.

MaxValue Child ID of Parent ID =

calculate( max( 'Data' [Value]), distinct( 'Data' [Unit]), distinct('Data' [Parent ID]))

Thank you ahead of time!

[Anonymous]

Hi @Tsunoda_2211,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are looking for a way to find the sum of unique ID's highest valued items within a unique quality. As @Ashish_Excel and @Deku both responded to your query, please go through the response and check if it solves your issue. 

I would also take a moment to thank @Ashish_Excel and @Deku, 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. 

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[Ashish_Excel · Accepted solution]

Hi,

These measures work fine

M = MAX(Data[Value])

Measure = SUMX(VALUES(Data[Unit]),[M])

Hope this helps.

[Tsunoda_2211]

Thank you, Ashish! This was far more simple than expected and works flawlessly. Probably shows I need some intro courses under my belt, but I wasn't aware of the SUMX function and that was extremely helpful. 

Thanks again!

[Ashish_Excel]

You are welcome.

[Ashish_Excel]

Hi,

These measures work fine

M = MAX(Data[Value])

Measure = SUMX(VALUES(Data[Unit]),[M])

Hope this helps.

[Deku]

Sumx(

Addcolumn(

Summarize(

Table,

Table[parent Id],

Table[unit]

),

"@max",

Calculate(

Max( table[value] )

),

[@max]

)