Calculated column, based on 3 other columns

artefarct

New Member
Joined
Feb 28, 2015
Messages
7
[FONT=&quot]Hi,[/FONT]
[FONT=&quot]I want to create a calculated column, "subcall duration", based on the three other columns. I think the exeample says it all. Or is a measure more appropriate?[/FONT]
[FONT=&quot]call type call nr duration subcall duration
call 1 4 50%
subcall 1 1 n/a
subcall 1 1 n/a
call 2 5 20%
subcall 2 1 n/a

etc etc.[/FONT]
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
i don't understand the example, you may need to either paste as a small html chart or explain where the 4 and 50% come from
 
Upvote 0
call typecall iddurationsubcall duration
call1450%
subcall11n/a
subcall11n/a
call2520%
subcall21n/a

<tbody>
</tbody>











Sorry, formatting scrambled. So I want to divide the duration of all subcalls by the duration of the (master)call with the same call id.
 
Upvote 0
As you indicated, a measure is better. Write these measures.
Total duration = sum(table[duration])
Subcall duration = calculate([total duration],table[call type]="subcall")
percent = divide([subcall duration],[total duration])

the last measure will work as long as it is in a pivot table with call ID on rows.
 
Upvote 0
the last measure will work as long as it is in a pivot table with call ID on rows.

Yes, but there are 240.000 call-id's..... So I want a formula which takes account of the call-id's. So I can use it in any pivot table.
 
Upvote 0
Ok. But what are you going to do with the numbers? You can't average percentages, so how will you use the percentages from these 240,000 IDs if you don't put them in a pivot table? How should they aggregate up?

there is an answer, but it depends on how you plan to use it.
 
Upvote 0
2 Solutions in Power Query:

1. 1 step, without Query Folding

Code:
= Table.AddColumn(PreviousStep, "subcall duration", (x) => if x[call type] = "call" then List.Sum(Table.SelectRows(PreviousStep , each [call type] = "subcall" and [call id] = x[call id])[duration])/x[duration] else null, Percentage.Type)

2. Split table in Calls and Subcalls, group Subcalls (these steps allow Query Folding). End Result without Query Folding: merge, calculate percentage, append subcalls.

Calls:
Code:
let
    Source = #"Calls and subcalls",
    #"Filtered Rows" = Table.SelectRows(Source, each ([call type] = "call"))
in
    #"Filtered Rows"

Subcalls:
Code:
let
    Source = #"Calls and subcalls",
    #"Filtered Rows" = Table.SelectRows(Source, each ([call type] = "subcall"))
in
    #"Filtered Rows"

Subcalls Grouped:
Code:
let
    Source = Subcalls,
    #"Grouped Rows" = Table.Group(Source, {"call id"}, {{"duration", each List.Sum([duration]), type number}})
in
    #"Grouped Rows"

End result:
Code:
let
    Source = Table.NestedJoin(Calls,{"call id"},#"Subcalls Grouped",{"call id"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"duration"}, {"subcall durations"}),
    #"Inserted Division" = Table.AddColumn(#"Expanded NewColumn", "subcall duration", each [subcall durations] / [duration], Percentage.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Division",{"subcall durations"}),
    #"Appended Query" = Table.Combine({#"Removed Columns", Subcalls}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"call id", Order.Ascending}, {"call type", Order.Ascending}})
in
    #"Sorted Rows"
 
Upvote 0
I just want a DAX formula that gives me the calculated column I want..... Nothing more , nothing less. From there a can use this column in any measure I want. And use this measure in any pivot table I want.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top