How do I sum payments from different payers but not charges?

Question & Answer
,
1

Here’s the data structure I have:

Claim ID | Charges | Payer name | Payer Level | Payments
123 | $1500 | ABC | primary | $1000
123 | $1500 | DEF | secondary | $500

The expected result is
Claim ID: 123
Charges: $1500
Payments: $1500

When I put this into a dashboard in Quicksight and use the agregate functions, it shows as:
Claim ID: 123
Charges: $3000
Payments: $1500

Tried to use different sum and sumover options, no success so far.

2

Hi @andreduarte and welcome to the QuickSight community!

To get the proper ‘charges’ amount, I would suggest trying using the maxOver aggregation, that way you can partition it by your claim ID.

Let me know if this works for your case or if you have any additional questions.

1 Like
3

I’ve tested this solution before but I think it doesn’t work since the data comes from different tables. I still get the wrong sum value.

Thanks for helping.

4

Hi @andreduarte,
When you say different tables, do you mean that your data is split up between multiple datasets?
When you’re using max, you shouldn’t be summing any numbers, it should just be pulling the max amount (so if there are multiple rows for one Claim ID and there ‘charge’ is the same, it will just take that amount instead of a total for all).

When trying out, what’s the sum value you get that is incorrect?

5

I have a left join between claim, charges and payments. I believe that this is returning multiple charges rows per claim and leading to the issue. I was able to use sumif(claim_charge, payer_level = 'primary) and it solved the problem. Thanks for your help.

1 Like