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.
Hi All
Is it possible to group the totals in a column so that it sums up all the values from one column into a new one by month and year, user & on a task without having to create a new table to achieve the outcome?:
Solved! Go to Solution.
Hi @rush,
Add an index column in Query Editor mode.
#"Grouped Rows" = Table.Group(#"Changed Type", {"Year-Month", "WorkCode", "User"}, {{"All rows", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
After applying above changes, remember to set the data type for [Total Hours] to whole number. Then, modify the DAX formula as:
Bench Test = VAR Maxindex = CALCULATE ( MAX ( Billing_Info[Index] ), ALLEXCEPT ( Billing_Info, Billing_Info[Year-Month], Billing_Info[WorkCode], Billing_Info[User] ) ) RETURN IF ( Billing_Info[Index] = Maxindex, CALCULATE ( SUM ( Billing_Info[Total Hours] ), ALLEXCEPT ( Billing_Info, Billing_Info[User], Billing_Info[Year-Month], Billing_Info[WorkCode] ), Billing_Info[WorkCode] IN { "Bench" } ), BLANK () )
Best regards,
Yuliana Gu
Hi @rush,
Please try:
TotalHoursBench = CALCULATE ( SUM ( Tablename[Total Hours] ), ALLEXCEPT ( Tablename, Tablename[User], Tablename[Yesr-Month] ) )
Regards,
Yuliana Gu
Thanks @v-yulgu-msft
It almost works but brings back duplicated total hours for each row rather than bringing back 1 total value per year-month, per user, per workcode.
Bench Test =
CALCULATE (
SUM ( Billing_Info[TotalHours] ),
ALLEXCEPT (
Billing_Info,
Billing_Info[UserID],
Dim_Date[MonthName_Year],
Billing_Info[WorkCode]
),
Billing_Info[WorkCode] IN { "Bench" }
)
Hi @rush,
Add an index column in Query Editor mode.
#"Grouped Rows" = Table.Group(#"Changed Type", {"Year-Month", "WorkCode", "User"}, {{"All rows", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
After applying above changes, remember to set the data type for [Total Hours] to whole number. Then, modify the DAX formula as:
Bench Test = VAR Maxindex = CALCULATE ( MAX ( Billing_Info[Index] ), ALLEXCEPT ( Billing_Info, Billing_Info[Year-Month], Billing_Info[WorkCode], Billing_Info[User] ) ) RETURN IF ( Billing_Info[Index] = Maxindex, CALCULATE ( SUM ( Billing_Info[Total Hours] ), ALLEXCEPT ( Billing_Info, Billing_Info[User], Billing_Info[Year-Month], Billing_Info[WorkCode] ), Billing_Info[WorkCode] IN { "Bench" } ), BLANK () )
Best regards,
Yuliana Gu
@v-yulgu-msft Thank you for your response. Much appreciated.
I cannot seem to get the Index to appear in my query:
My current query below:
let Source = Table.Combine({#"Mar 2017", #"Apr 2017", #"May 2017", #"Jun 2017", #"Jul 2017", #"Aug 2017", #"Sep 2017", #"Oct 2017", #"Nov 2017", #"Dec 2017", #"Jan 2018", #"Feb 2018", #"Mar 2018", #"Apr 2018", #"May 2018", #"Jun 2018", #"Jul 2018", #"Aug 2018", #"Sep 2018", #"Oct 2018", #"Nov 2018", #"Dec 2018", #"Jan 2019", #"Feb 2019"}), #"Filtered Rows" = Table.SelectRows(Source, each ([UserName] <> null and [UserName] <> "Admin Admin")), #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","(Pty) Ltd","",Replacer.ReplaceText,{"ClientName"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Pty","",Replacer.ReplaceText,{"ClientName"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","(PTY) LTD","",Replacer.ReplaceText,{"ClientName"}), #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Ltd","",Replacer.ReplaceText,{"ClientName"}), #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","(PTY)","",Replacer.ReplaceText,{"ClientName"}), #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","PTY LTD","",Replacer.ReplaceText,{"ClientName"}), #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Limited","",Replacer.ReplaceText,{"ClientName"}), #"Filtered Rows1" = Table.SelectRows(#"Replaced Value6", each [UserID] <> 2024), #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "YYYY_MM", each Date.ToText([Date], "yyyy-MM")), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"YYYY_MM", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"YYYY_MM", "WorkCode", "UserName"}, {{"All rows", each Table.AddIndexColumn(_, "Index",1,1), type table}}) in #"Changed Type"
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |