1 Reply Latest reply on Aug 13, 2018 6:54 PM by swaroop.gantela

# Need to perform a calculation from 2 unrelated Data Sets

Hello Everyone!

My team just bought Tableau and we are really excited to start implementing it in our systems! I can tell this forum is going to be really helpful so thank you all in advance!

With that, I do need some help! I need to calculate a close ratio. Close Ratio = #Items Sold / #Quotes. I am working with an insurance company and this ratio is important for development purposes. However, the numerator and denominator data come from 2 very different sources. I cannot make a union or a join (at least I don't think) which is why I can't get Tableau to perform the calculation. Selling and Quoting are two very different parts of the business. Customers are different and even the columns that should be very similar (Products and Sales people) are labeled differently between the data sets.

The vision: I need to be able to perform this calculation dynamically. I need to not only find the Close Ratio overall, I also need to be able to find the Close Ratio for a given SALES PERSON, for a given PRODUCT, on a given DAY. I understand this will mean doubling up on filters for both the Numerator and Denominator. Unless I can find a way to make a filter for DATE, PRODUCT and SALES PERSON work on both data sets (the Sales data and the Quote Data)? That's something else I need to explore.

Either way, how can I make this calculation work? Is this possible? Its really just summing data from one Data Set and Dividing it by the Sum on another Set.

Attached is a Mock Up of both a Quotes and Sale report.

Thank you so much for your help guys!

Ethan

Overall: Close Ratio = #Items Sold (for a given sales person, on a given day, for a given product) / #quotes (for a given sales person, on a given day, for a given product)

• ###### 1. Re: Need to perform a calculation from 2 unrelated Data Sets

Ethan,

Welcome to the Forum.

Though your question seems similar to this one:

there may be some differences with your setup.

In the example in the link, rather than union or join, the two sources were blended:

your specific example is given as a scenario for when one might want to blend.

After the blending on SalesPerson and Product, the Close Ratio was calculated as:

SUM(Sales)/SUM([QuotesSheet].[Quotes])

showing that the Quotes Sum came from the other sheet.

It looked like the above screenshots were of fake data.

If so, would be grateful if you would post that file,

or a workbook connected to that file.

1 of 1 people found this helpful