Custom Column Help

[zeke101]

Hey Guys, 

I've been tasked to find what the impact speed is for processing our tasks with and without automation. 

We collect data on both Manual and Automated tasks.  The Automated tasks are followed by the manual tasks that cant be automated. Each task is measured in time (seconds), but I need my Automated tasks to show the avg. time of my manual tasks so that I can get a total time assuming the automated tasks were done manually..... 

I'm thinking that creating a new column that replaces the current Automated time with the manual Avg time (for that task) and keeping the manual times the same will do the trick.  

Below I've provided a grid of what I think my table should look like -Green being the new column result - Notice in the new column, Manual tasks are the same as the actual column and the automated tasks are avg for the manual task specific to that task (task1, task2, task3).

Appreciate the help. 

[ryan_mayu · Accepted solution]

@zeke101 

you can try to create a column like this

Column = 
VAR task="Mannual "&right('Table (2)'[Tasks],len('Table (2)'[Tasks])-find("Task",'Table (2)'[Tasks],1,0)+1)
RETURN if(FIND("Mannual",'Table (2)'[Tasks],1,0)<>0,'Table (2)'[Acutal Time Seconds],AVERAGEX(FILTER('Table (2)','Table (2)'[Tasks]=task),'Table (2)'[Acutal Time Seconds]))

[v-deddai1-msft · Accepted solution]

Hi @zeke101 ,

You can use the following m query to split your task column to two colums(Task type and Task number):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wciwtyc9NLElNUQhJLM42VNJRMlSK1YlW8k3MK03MgQsaG4BFUVUbYVMNEjTBptoYm2qwIDbVOF1iRJJLDI1JcompUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, #"Actual Time Seconds" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task", type text}, {"Actual Time Seconds", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.PositionOfAny([Task],{"0".."9"})),
    #"Inserted First Characters" = Table.AddColumn(#"Added Custom", "First Characters", each Text.Start([Task], [Custom]), type text),
    #"Inserted Text Range" = Table.AddColumn(#"Inserted First Characters", "Text Range", each Text.Middle([Task], [Custom], 1), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text Range",{{"First Characters", "Task type"}, {"Text Range", "Tasknumber"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Custom"})
in
    #"Removed Columns"

Then use the following calculated column:

Column = IF('Table'[Task type] = "Automated Task",AVERAGEX(FILTER('Table','Table'[Tasknumber] =EARLIER('Table'[Tasknumber]) &&'Table'[Task type] = "Manual Task"),'Table'[Actual Time Seconds]),'Table'[Actual Time Seconds]) 

For more details, please refer to the pbix file:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EX1YRC4AdOdKpf59-f...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[ryan_mayu · Accepted solution]

@zeke101 

you can try to create a column like this

Column = 
VAR task="Mannual "&right('Table (2)'[Tasks],len('Table (2)'[Tasks])-find("Task",'Table (2)'[Tasks],1,0)+1)
RETURN if(FIND("Mannual",'Table (2)'[Tasks],1,0)<>0,'Table (2)'[Acutal Time Seconds],AVERAGEX(FILTER('Table (2)','Table (2)'[Tasks]=task),'Table (2)'[Acutal Time Seconds]))

[AllisonKennedy]

@zeke101 

You can do this if you have a clear way to distinguish Automated vs Manual Task. 

Take this formula for example: 

Average Manual Time = IF(AutomationTasks[Automated]= "Manual", AutomationTasks[Actual Time], AVERAGEX(FILTER(ALL(AutomationTasks), AutomationTasks[Task]=EARLIER(AutomationTasks[Task]) && AutomationTasks[Automated]="Manual"), AutomationTasks[Actual Time]))

Works when your data looks more like the attached sample file (see below signature).

If you need help getting your data to look like that, please provide more accurate description of what your task names look like and how we can tell if they are automated or manual.