Creating a summary of one field based on a value in any of 3 other fields
Note: Dx is short for diagnosis
My issue is that I have a table with Paid Amount and I want to slice it by Diagnosis codes. Trouble is, there are three different columns for diagnosis codes (DxCd1, DxCd2, DxCd3), any one of which might contain the value I am trying to group by. I can't figure out how to sum a numeric value by grouping rows based on data in more than one column.
Specific example: The report is Top 10 Dx Codes by Paid Amount, based on whatever filters have been selected. This is super easy to do with one Dx code: Without filters, the top CPT code is 94123 and Paid Amount totals $100K. But 94123 also appears in DxCd2 and those line items sum to a Paid Amount of $50K. How can I show that CPT code 94123 is associated with $150K of claims?