MULTIPLE calculate(sum()) columns over same area (Circular dependency issue)

[Anonymous]

Hello everyone,

I have a timesheet report that displays as follows, where each row represents an entry (each job logged).  There are columns (Mon-Sun) with the entered hours, and then columns for comments for each day which represent the "type" of job code (A, W, D, I, R, F).

I am looking for a way to total the hours for each "type", relative to the row it is in.  I tried making a column: "Type A = calculate(sum('Timesheet'[Mon]),'Timesheet'[Mon - Comment]="A") + calculate(sum('Timesheet'[Tue]),'Timesheet'[Tue- Comment]="A") + etc....... which accomplished what I wanted for type A.  But as soon as I tried to make the next column for the next type: "Type W = calculate(sum('Timesheet'[Mon]),'Timesheet'[Mon - Comment]="W")etc....., I was given a "circular dependency" error.

Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total	Mon - Comment	Tue - Comment	Wed - Comment	Thu - Comment	Fri - Comment	Sat - Comment	Sun - Comment
8	8	8	--	--	--	--	24	I	D	R	--	--	--	--
1	3	--	--	--	--	--	4	A	A	--	--	--	--	--
--	3	--	--	--	--	--	3	--	R	--	--	--	--	--
7	2	--	--	--	--	--	9	R	I	--	--	--	--	--

In excel, I was able to accomplish this with the following formula, but am looking for the equivalent solution in Power BI. 

Thank you!

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PU9JRCilNBZLhqSkgdkYpkHQrygSSwYklILIUrCa/JDEHSAN1KOgqOOfn5qbmlUD0ovCBpqDKZ5Si8IEmo/CBdqDyS5HNj9WJVrIAisKwri4mYWQCJDyB2AWIg7AqAhljCGQa4zACTICMcYRiHIpA5oDZeA2CS2J3DNwkc5Dj8RlkCTXDE485sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t, #"(blank).11" = _t, #"(blank).12" = _t, #"(blank).13" = _t, #"(blank).14" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","--",null,Replacer.ReplaceValue,{"Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Total", "Mon - Comment", "Tue - Comment", "Wed - Comment", "Thu - Comment", "Fri - Comment", "Sat - Comment", "Sun - Comment"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Mon", Int64.Type}, {"Tue", Int64.Type}, {"Wed", Int64.Type}, {"Thu", Int64.Type}, {"Fri", Int64.Type}, {"Sat", Int64.Type}, {"Sun", Int64.Type}, {"Total", Int64.Type}, {"Mon - Comment", type text}, {"Tue - Comment", type text}, {"Wed - Comment", type text}, {"Thu - Comment", type text}, {"Fri - Comment", type text}, {"Sat - Comment", type text}, {"Sun - Comment", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "A", each (if [#"Mon - Comment"]="A" then [Mon] else 0) + 
(if [#"Tue - Comment"]="A" then [Tue] else 0) + 
(if [#"Wed - Comment"]="A" then [Wed] else 0) + 
(if [#"Thu - Comment"]="A" then [Thu] else 0) + 
(if [#"Fri - Comment"]="A" then [Fri] else 0) + 
(if [#"Sat - Comment"]="A" then [Sat] else 0) + 
(if [#"Sun - Comment"]="A" then [Sun] else 0))
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

Note: I only added the "A" column.  You can how it is done, and can add the other ones yourself .