1 Reply Latest reply on Jul 26, 2018 7:57 PM by swaroop.gantela

Calculations using only certain Dimension values

This feels like it should be pretty simple but I rarely use Tableau and keep thinking in terms of Excel.  I'd like to calculate a close ratio per row, followed by a close ratio for calendar year for each agency, and then a total of the agencies shown.

However I need to total all values in the Stage dimension EXCEPT for Closed Withdrawn. Then I need to take just the total for Closed Won as the numerator to calculate the close ratio.  For example, AB & Associates Q3 2017 should show a close ratio of 65% (13 closed won/20).

How do I do this?

I am in version 10.3.3

Thanks,

• 1. Re: Calculations using only certain Dimension values

Suzanne,

I think you can try Level of Detail Calculations to obtain this:

Numerator:

{ FIXED [Brokerage Firm Rollup],[Effective Date (Years)],[Effective Date (Quarters)]:

COUNTD(IF [Stage]="Closed Won" THEN [Account Name (copy)] END)}

Denominator:

{ FIXED [Brokerage Firm Rollup],[Effective Date (Years)],[Effective Date (Quarters)]:

COUNTD(IF [Stage]<>"Closed Withdrawn" THEN [Account Name (copy)] END)}

Note: I added the two date calculations using Custom Dates: