I am trying to get a "Cumulative CPA" for the last 5 "Integrations" as shown in the picture below.
Essentially grabbing observations for the last 5 integrations (All rows starting from 5/15/17). Is there any way to create a calculated field for this?
Hi,
Check the workbook I've attached.
There's probably a cleaner method to do this!
Here is a breakdown of the calculations:
# of Integrations: This is just a calculation I created to mimic your # of Integrations measure/dimension on your grid, you can replace this measure/dimension with the one on your grid.
Total: This takes the total sum of each partition, in this case our partition is the entire grid so it brings us the total count of integrations, which is 8.
WINDOW_SUM([# of Integrations])
Running Sum of # of Integrations: This is a running sum of the integrations which is going to be used in the next calculations to determine how far away we are from the last 5 integrations.
RUNNING_SUM([# of Integrations])
# of Integrations Bucket: This is just an If statement that labels the last 5 integrations.
IF [Total]-[Running Sum of # of Integrations] <= 5 THEN 'Last 5 Integrations Total'
ELSEIF [Total]-[Running Sum of # of Integrations] > 5 THEN 'All Other Integrations'
ELSE NULL
END
Cumulative CPA: This is an If statement that determines when to sum Sales. Sales was just an arbitrary measure that I used to show the calculation, you can replace SUM([Sales]) with the measure you're trying to show.
IF [Total]-[Running Sum of # of Integrations] <= 5 THEN
WINDOW_SUM(
IF [Total]-[Running Sum of # of Integrations] <= 5 THEN
SUM([Sales])
ELSE NULL
END)
ELSE RUNNING_SUM(SUM([Sales])) END
Show Last 5 and Others: This is a nested Case-If statement that filters the view so you only see the integrations that are above the last 5 and the total of the last 5.
CASE
(IF [Total]-[Running Sum of # of Integrations] <= 5 THEN INDEX()
ELSE 1
END)
WHEN 1 THEN 1
WHEN SIZE() THEN 1
END
Again there's probably a better method of doing this and you can probably clean the calculations up a bit, so feel free to change the calculations depending on your specific need.
