Pivot use Running Total in Calculated Field

Hi.

Iīve attached a sample file where I have some "cohort" data style for a set of customers that became customers back in Jan2018, and their 'Activity month' (1 to 22 so far). Then I have their respective monthly revenue as well as monthly costs. From the revenue and costs I have created two "Running Total in" fields in the pivot to get the cumulative revenue and costs.

PROBLEM:

With the running revenue and costs I now want to calculate the "Running life time value" of the customers, as well as the return on investment to see when and if we made our money back at some point from this set of customers. My problem now is that I canīt find a way of actually using the "Running Totals" in the pivot, in a calculated field. Is the pivot really that limited?

I have made two (yellow headers) columns with the result manually calculated to show what I want to somehow apply in the pivot instead.

N.B I already thought about altering the source data, but the data will have many markets, business partners etc to eventually filter out, so I donīt see how some "SUMIFS" could do the trick, hence I want to do it straight in the pivot.

Can anyone come up with a solution to this, or a work around?

No attachment?

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Also, is your Excel version up to date in your profile - are you still using Excel 2007?

Load your data to the Data Model (using Power Query), then create your Pivot Table based on the Data Model.

Now you can create explicit measures, using DAX - in your case, something like these (making a few assumptions on your calculations):

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Note: I think your manually created ROI Running Total is wrong - you're calculating a running total of a running total, for both Revenue and Commission...


See attached workbook for worked example.