4 Replies Latest reply on Jun 26, 2017 6:20 AM by Michael Gillespie

# Sum issue moving from client sum to month sum

I have attached a workbook that I'm having trouble with. I've been using dummy data to make a proof of concept for KPI tracking. I want to calculate client retention rate, we do this by looking at month over month lost client revenue for the past 3 months divided by the current month total revenue. I have a client, month, and year view right now. I was trying to break the formula up and first calculate the total lost revenue for each client, which I do have on the client view labeled Lost Revenue. But when I remove the Client dimension the formula breaks. I'd like to be able to view this for the client for month and for year and can't figure it out! Any help is much appreciated!

• ###### 1. Re: Sum issue moving from client sum to month sum

Brittany, can you repost your TWBX but do an extract first?  That workbook is trying to connect to your Google account.

• ###### 2. Re: Sum issue moving from client sum to month sum

Sorry about that! Hopefully this one works properly now!

• ###### 3. Re: Sum issue moving from client sum to month sum

No worries.  I'll look at this shortly.

Thanks for reposting.

• ###### 4. Re: Sum issue moving from client sum to month sum

OK, finally got a few minutes to look at this!

Problem number 1: you don't actually have a date field anywhere, and you need one to make this work.  You have a Year, but it's really just a number, not a date.  You have a Month, but it's just a text field and it's not related to the Year field.

So, I created a DATE field, and assigned an arbitrary value of '1' to the DAY component.  That gives us a full date that Tableau can make use of!

Problem number 2: your MoM Revenue is not calculating right.  Part of the solution is the DATE field, and part of it is the way the table calc is working.  You need to edit it.

In the attached workbook, on Sheet 4, I've started to solve this for you.

I created a new calculation - Month to Month Revenue Change.  This does things slightly differently from your MoM Revenue field but gets the right result.  The BIG trick is in telling Tableau HOW to calculate that value.

On the Marks card, right-click on either your MoM field or my Month to Month field and select "Edit Table Calculation".  I selected "Specific Dimensions", made sure 'Month of Date' is selected, then under "Restarting every", I chose "Client".  This tells Tableau to start its calculation over whenever the Client dimension changes.

This gets you the result you want, I think.

Try to take it from here and see where you end up.  Ask more questions if necessary!