Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
63 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |