4 Replies Latest reply on May 10, 2013 12:46 PM by Ryan Johnson

# More Ratio Help

Thanks to Shawn, I've got my Win/Loss ratio cooked up in my other worksheets.  But now I'm encountering a different problem.  Before, I was calculating my ratio off of distinct records using an ID field  (QUOTE_ID).  But now the table I'm using to create my ratio has duplicate QUOTE_IDs.  Is there some way to nest a COUNTD function within a calculated field?  I created a calculated field using COUNTD([QUOTE_ID]) but I'm not sure how to use it to get my ratio of quotes won vs lost.  It does give me the correct number of IDs within each row when I plug it in as a measure.

Any ideas?  File attached...

-Ryan

PS Even though the file says "for Shawn" people not named Shawn are welcome to reply as well.

• ###### 1. Re: More Ratio Help

In the 10,945 rows of QUOTE_ID there are no duplicate records. Where is the duplication coming from? Please demonstrate/explain how you're getting a wrong count. Please add the the win/loss ration to the viz so I can see how you're wanting to use it. Thanks,

--Shawn

• ###### 2. Re: More Ratio Help

Ryan, T is giving you 2% (actually 2.38%) because that's what 1 win divided by 42 records equals. You haven't told T that you only want to count unique Quote_ID records, so T is giving you this:

To fix this all you have to do is change your denominator from counting records to counting unique IDs like this:

SUM([Wins])/COUNTD([QUOTE_ID])

This produces the 3.45% you're looking for.

Cheers,

--Shawn

• ###### 3. Re: More Ratio Help

Shawn, that's twice you've saved my bacon now.  Thanks for giving this rookie some tips and tricks!

Much obliged,

Ryan

• ###### 4. Re: More Ratio Help

Happy to help. Always nice to hear a bit of encouragement, thanks.

--Shawn