4 Replies Latest reply on Nov 14, 2017 2:53 PM by laura.vanzutphen

# Difference between percent of total and previous percent of total

Hi all,

In my workbook (see attached) I am calculating the percent of total across different types of products. So for every week, I can see how much of the sales can be attributed to product A, product B and product C.

Now I want to compare this to the previous week, so that I am able to say: "product A consisted of 21% of total sales this week, it was 20% of sales last week, so the percentage increased with 1".

However, if I define 'last week' as

LOOKUP(SUM([Amount]),-1)/LOOKUP(TOTAL(SUM([Amount])),-1)

then it needs Table (down) to calculate the correct percentage of product A, but it needs Table (across) to get the previous week. However, Tableau does not allow me to do this simultaneously.

Am I doing something completely wrong? Or how can I achieve this?

So just to be sure, I would like to achieve this:

Week 1
Week 2Week 3
Type AThis week = 20%

This week = 21%
Last week = 20%
Difference = +1%

This week = 19%

Last week = 21%

Difference = -2%

Type BThis week = 30%

This week = 31%
Last week = 30%
Difference = +1%

This week = 29%
Last week = 31%
Difference = -2%
Type CThis week = 50%

This week = 48%

Last week = 50%

Difference = -2%

This week = 52%

Last week = 48%

Difference = +4%

• ###### 1. Re: Difference between percent of total and previous percent of total

(V 10.1 here)

I'm looking at this.

• ###### 2. Re: Difference between percent of total and previous percent of total

I don't understand what you are doing with that TOTAL() in there.  It doesn't match what you are describing in your question.

• ###### 3. Re: Difference between percent of total and previous percent of total

I get it.

OK.  Break the calcs into separate calcs.  I have:

Now you can tell Tableau to do one calc one way and the other calc the other way.  Here is where you do that:

For [Last week - amount] I have Table(across), but when you click on [Total for week] in that pull-down you'll see that I have it set to Table(down)

See attached.

2 of 2 people found this helpful
• ###### 4. Re: Difference between percent of total and previous percent of total

Thank you, that was very helpful!