9 Replies Latest reply on Dec 4, 2013 11:54 AM by Matt Lutton

    Percent calculation with blended data

    Thomas Beaulieu

      Hello - I have the following data excerpt that I would like help with.  I have two data sources, Avante which contains shipment data and EVVQuotes which contains quotation data; both are xlsx format.  My employer competitively bids on business so the EVVQuotes file has many more rows of data than the Avante shipment file because we only succeed in booking about 30% of the projects we quote.  The Avante shipment file is richer than the EVVQuote file in that it has many more columns of data but fewer rows as stated above.

       

      I have customer names than appear in both data bases.  I also have customer names that appear in EVVQuote but not Avante because although they ask for bids they never buy from us.

       

      For each customer name I am trying to calculate the hit rate by dividing Avante total shipments by EVVQuote total bids. 

       

      My data sources are blended on quotation numbers.  There are many more distinct quotation numbers in EVV quote than in Avante.  For some reason, I cannot get correct results when I double check what seems like a simple calculated field which is written as sum(Avante shipments)/sum(EVVQuote bids).  This formula calculates the correct denominator but the numerator comes up way short.  I think it only counts Avante shipments for which there is a common quote number between both databases.  I want the numerator to calculate total Avante shipment regardless of any commonality between databases.  Any idea how to make this happen?

       

      I am happy to provide more detailed views of the data if necessary.

       

      Capture1.PNG.png