Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to 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] ))
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] ))
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?
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
52 |