What is the data source you're connecting to?
Is it possible for you to change context to retrieve different values?
I.e. if you're doing a commission report, can you query a view which contains the duplicated / triplicated revenue data by sales person? And similarly, if you need to run a company revenue report, do you have a view which only contains the 1 sales record?
unfortunately I cannot change the context, the only solution I can see is to use tableau to filter out the duplicate tag_no's since the only number I am after in the record is the revenue number. I guess a better way to put it is to say I need the revenue number per unique tag_no
You could use the MIN / MAX instead of SUM if you are looking at the data at the invoice / row level. I assume you're probably going to be looking at a higher level than this, like total company, in which case this won't work.
Can you attach a packaged workbook?
You could achieve this with Custom SQL by applying the DISTINCT clause.
I have attached a sample, looking at the data below:
each recond has a unique Tag_no, except the last three which share the same number. I am trying to include all the unique tag_no, and a single representative of any that are duplicated - so as the table sits now tag _no 29 equates to 450 in commission (150 * 3), while I need it to only equate to 150
sample.twbx 23.0 KB
I assume Excel is your data source then based on your example.
I would use custom sql. Right click the data source, Edit Connection and select custom sql.
It should generate a select statement based on the columns.
I would look to do something like this:
- SELECT DISTINCT (will select only the distinct records)
- Remove the account column (if you have three different accounts for the same tag no, these would all be 'distinct' and you'll still have the duplicates)
[Sheet1$].[Commisssion] AS [Commisssion],
[Sheet1$].[F1] AS [F1],
[Sheet1$].[Last Trade color] AS [Last Trade color],
[Sheet1$].[Number of Records] AS [Number of Records],
[Sheet1$].[Tag_no] AS [Tag_no],
[Sheet1$].[Trade Date] AS [Trade Date]
excel is the data source for the sample, sql is the data source for the actual tableau tables. This particular table is part of a larger workbook and I cannot modify the dataset. In other words I am stuck trying to come up with some way to ignore the duplicate records in tableau
I'm not sure you're going to be able to do it.
As I said earlier, if you're looking at the row level data, you can run a MIN or MAX with the TagNo on the rows shelf and you'll get the number you want. Using the same approach you could create a calculated field which is Commission / count(tag_no), but this will only work when you're at the TagNo level. You want to roll up to account and the context of the calculation changes and it will be wrong.
Given that you are using two different contexts here (1: Account level Commission, 2: Company Revenue) I think the only way you get to what you want is by having two connections in your workbook. 1 like it is now, the second with the distinct clause.
not sure I am explaining this correctly, looking at the table above - I am wanting to total the commissions for the accounts in the account names column, ignoring any of the rows that contain a duplicate tag_no
I still think you're going to need to use a DISTINCT select, just including the account as well.
coming at it from a different direction - could I somehow add up all the par amounts for a tag_no and then divide by the count for that tag_no
See post 7 of mine above
"Using the same approach you could create a calculated field which is Commission / count(tag_no), but this will only work when you're at the TagNo level. You want to roll up to account and the context of the calculation changes and it will be wrong."
If you made a report with this calculation and the TagNo, it will calculate correctly.
If you remove the TagNo from the report and try view it at Account level, it will calculate the total of the commission / the count of all tags numbers. It's effectively going to calculate it at the level Account level which is wrong. Hence why you can't use this.