Would be very grateful if anyone could help me with the following. I suspect there's a very simple solution but I've just not been able to get to it.
Summary: Using Tableau 8.0. Have two excel files linked together. 1) Target.xls which shows a sales persons targets 2) Achievement.xls which shows a sales persons achievement
The target file contains the following fields...So far it has three months worth of Data - so the month can be April, May or June.
Salesperson: Mr Smith
Product Name: Product XYZ
Target Value: 5000 USD
The achievement file has a different field structure as it's pulled from a different database. But the upshot is the Salesperson/Month/Product name fields are in common - so I link using those fields.
Now I've managed to link the files. And I also am able to drag and drop my fields to get the tables I want - with the numbers correctly popluated. So for example a table that looks like this
MONTH SALESPERSON PRODUCT NAME TARGET ACHIVEMENT CALCULATED FIELD WANTED HERE
April 2013 Mr Smith Product 1 5000 USD 8000 USD
April 2013 Mr Smith Product 2 3000 USD 2000 USD
May 2013 Mr Smith Product 1 3000 USD 1000 USD
May 2013 Mr Smith Product 2 4000 USD 9000 USD
Now here's where I get stuck. I want to use a calculated field to add another column to the extreme right - which will simply work out the target vs achievement percentage.
In the calculated field I use the formula [ACHIEVEMENT]/[TARGET]
Now this does give me some values in the table - but they are incorrect. Comes out with strange percentages. I think the reason is because the formula uses all the cumulative target values and the cumulative achievement values - i.e. it doesn't take into account my month column - and so not dividing the correct numbers.
So how do I get the calculated formula to acknowledge the existence of the month column in the table - and give me the correct percentages accordingly?
Any assistance would be much appreciated.
Thanks in advance.