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

    Calculations using only certain Dimension values

    Suzanne Parr

      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
          swaroop.gantela

          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:

          Custom Dates

           

          Please see workbook v10.3 attached in the Forum Thread.

           

          276932close.png