Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |