How to create a single calculated field from multiple column filtering

Question

What I want?

I have 3 columns (2 dimensions and 1 measure) and I want to use the measure column based on some of the string occurrences from the dimensions columns.

Sample columns: 'Event', 'Screen' and 'Time'

Sample Dataset: (NOTE: There are many other values along with these, but I require only the ones in the lower cases i.e., NULLs and NAVIGATIONs are to be excluded)

I want a single calculated field with three steps that yields as below:

(2*count of "name submitted" occurances in Event) - (AVG time of corresponding "name submitted" (from Event) * count of "name submitted" occurances in Event)
+

(2*count of "address added" occurances in Event) - (AVG time of corresponding "Add address" (from screen) * count of "address added" occurances in Event)
+

(2*count of "order created" occurances in Event) - (AVG time of corresponding sum of "orders"+"order detail"+"order confirmation" (from screen) * count of "order created" occurances in Event)

My approach:

I have dragged Event and Screen dimensions to the filter pane and selected all the values including NAVIGATION AND NULL (as these fields correspond to the time that I need in the calculation) and it didn't quite work!

I have also created PARAMETERS for Event and Screen for each of the three steps of the calculation (above) and it didn't work either!

So, what would be a best way to achieve the above calculation?

Any help is much appreciated.

Answer 1

Try with this calculated field:

(
    (2 * SUM(IIF([Event] = 'Name submitted',1,NULL)))
    - 
    (
        AVG(IIF([Event] = 'Name submitted',[Time],NULL))
        * SUM(IIF([Event] = 'Name submitted',1,NULL))
    )
)

+

(
    (2 * SUM(IIF([Event] = 'Address added',1,NULL)))
    -
    (
        AVG(IIF([Screen] = 'Add address',[Time],NULL))
        * SUM(IIF([Event] = 'Address added',1,NULL))
    )
)

+

(
    (2 * SUM(IIF([Event] = 'order created',1,NULL)))
    -
    (
        AVG(IIF([Screen] = 'orders'
                OR [Screen] = 'order detail'
                OR [Screen] = 'order confirmation'
            ,[Time],NULL))
        * SUM(IIF([Event] = 'order created',1,NULL))
    )
)