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?
1 of 1 people found this helpful
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]
% collateral field changed to: COUNTD([Cust Status])/[Customers Distinct Count]
Thanks Andy for your help Andy Piper