Create Summarized Table Based on Value of One Table and Max of Another

[gulberg]

Hello,

I am trying to create a new table (not a measure) by way of DAX, where I want to have a new column summarized by one column (Effor Type) and display sum of MAX Values based on another column (Effort Class)
I have this as the main table:

ID	Effort Type	Effort Class	Calculated Value
401	EFFORT	Complexity	2.5
401	EFFORT	Complexity	2.5
401	EFFORT	Complexity	2.5
401	EFFORT	Budget	4
401	EFFORT	Budget	4
401	EFFORT	Budget	4
401	EFFORT	Resourcing	4
401	EFFORT	Resourcing	4
401	EFFORT	Resourcing	4
401	EFFORT	Timing	4
401	EFFORT	Timing	4
401	EFFORT	Timing	4
401	EFFORT	Sustainment	4
401	EFFORT	Sustainment	4
401	EFFORT	Sustainment	4
401	READINESS	Internal Resourcing	2.5
401	READINESS	Internal Resourcing	2.5
401	READINESS	Internal Resourcing	2.5
401	VALUE	Impact	8
401	VALUE	Impact	8
401	VALUE	Impact	8
401	VALUE	Cloud	0
401	VALUE	Cloud	0
401	VALUE	Cloud	0
401	VALUE	Regulatory	0
401	VALUE	Regulatory	0
401	VALUE	Regulatory	0
401	VALUE	UE	8
401	VALUE	UE	8
401	VALUE	UE	8

I want the summarize table as follows;

ID	Effort Type	Totals
401	EFFORT	18.5
401	READINESS	2.5
401	VALUE	16

Any help is much appreciated.

[HotChilli · Accepted solution]

I would probably do this in Power Query where it would be 2 'Group By' transforms.

A new table in DAX would be :

TableWE = var _preTab = SUMMARIZECOLUMNS(TableYTest[ID],  TableYTest[Effort Class], TableYTest[Effort Type], "Amoun",  MIN(TableYTest[Calculated Value]))
RETURN
GROUPBY (_preTab, TableYTest[ID],  TableYTest[Effort Type],  "Spec Amount", SUMX ( CURRENTGROUP (), [Amoun] ))

[gulberg]

Thank you very much @HotChilli I am a newbie so can you please expand on why you would Group by in Power Query instead of doing this in DAX?