3 Replies Latest reply on Jul 15, 2018 1:27 AM by Sally Safa

    Percentage calculations

    Sally Safa

      Hello,

      I am working on a set of data where I have multiple locations and multiple customers within each location.

       

      Each customer has some collateral (it can be cash, share, real estate or other - any value in any of these fields means the customer has a collateral)

       

      Also, each customer has a specific limit amount.

       

      I am trying to build two different views for:

       

      One: I want to display only the percentage of customers having collateral per location (No.  of customers with collateral in a location / total number of customers in that specific location)

       

      Second: In another view, I want to display the limit concentration percentage of customers with collateral per location - in other words, for all customers having a collateral I will divide the total limit for these customers by the total limit of all customers in the same location and display the percentages for each location.

       

      I attached a sample workbook - I am using Tableau Desktop version 2018.1

       

      Thank you for your time in advance

        • 1. Re: Percentage calculations
          Andy Piper

          Sally,

           

          If I understood what you were trying to accomplish, I believe the attached workbook will provide you a start.

           

          Andy

          1 of 1 people found this helpful
          • 2. Re: Percentage calculations
            Sally Safa

            Thank you Andy!

             

            This was definitely helpful.

             

            However I am facing an issue with the percentages because sometimes for the same location I have multiple rows for the same customer ID, so I need to sum up the collaterals for each client and count the customer only once, else the percentages are going beyond 100.

             

            Just to clarify same customer will appear in multiple rows with different collaterals that's why I need to sum up all.

             

            Is it possible to help me with that please?

            • 3. Re: Percentage calculations
              Sally Safa

              Hey - no worries about the previous query I figured it out.

               

              For those facing the same issue, below are the changes I made to Andy's workbook above in order to calculate the correct percentages:

               

              Cust Status field:

               

              IF ([Cash Collateral USD]+[Other Collateral USD]+

                  [Real Estate Collateral USD]+[Share Collateral USD])>0

              THEN [Customer No]

              END

               

              % collateral field changed to:  COUNTD([Cust Status])/[Customers Distinct Count]

               

              Thanks Andy for your help Andy Piper

              1 of 1 people found this helpful