Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |