1 Reply Latest reply on Jul 26, 2018 1:32 AM by Archana Ganeshalingam

# Calculation without filters affecting it

Hello all,

I have been working on this for a while and I still can't make this work. I hope you can give me a hand.

It is a bit complicate to be explained but I will do my best.

So the objective is to get 3 things:

2. Spend at campaign level = campaign_spend

3. CPA at advertiser level = sum(total conversions for specific activities)/advertiser_spend (calculated in point 1)

4. CPA at campaign level =  sum(total conversions for specific activities)/campaign_spend (calculated in point 2)

Data is not user friendly. Granularity goes like this:

Advertiser > Campaign > Placement > Platform > Activity

Spends are defined at placement level but due to the granularity and the joining we have, this spend appears duplicated (times combination of the number of platform and activity we have).

ISSUE 1: I don't manage to get spend at campaign level:

Steps I am following:

1. .Calculate the max at placement level per campaign and advertiser

2. Sum values in point 1

I almost manage to do it using running_total. Issue is I want only a number per campaign but the max is calculated at placement level: Wanted output: spend campaign1 = 20

ISSUE 2: CPA calculation at campaign level

CPA is calculated as sum(total conversion for specific activity values) / total_campaign_spend

There are two issues here:

1. total_campaign_spend is the issue number 1 I listed

2. I need to use activity as filter only affecting the total_conversion field in the CPA calculation: I mean, if the campaign_spend = 20£, it doesn't matter the activity I select, this shouldn't affect the campaign_spend.

Does it make sense?

I hope it does.

I attached the data I am playing with.

Thank you very much. Any help will be super welcome.

Cheers

Patricia

• ###### 1. Re: Calculation without filters affecting it

Hi Patricia,

I think what you are looking for a is a FIXED LOD (level of detail calculation). These are not affected by subsequent filters because they are earlier in Tableau's query pipeline.

I tried to recreate some of what you explained in the workbook attached but it's hard to verify the results with the sample data you sent though!

Cheers,

Archana