Working with Duplicate Rows

[aflintdepm]

I am trying to create a report to reconcile charges with payments.  This is a healthcare environment that generates 2-3 charges for each claim, depending on the number of insurances.  Unfortunately, the billing system does not assign a unique charge number or indicate who the charge is going to.  Here is an example:

Claim    Name             Amount

12345	John Smith	250
12345	John Smith	250
12345	John Smith	250
45678	Bob Jones	300
45678	Bob Jones	300
45678	Bob Jones	300

In this example, each patient has a Primary Insurance, a Secondary Insurance, and a Co-pay.  Each row represents a claim to each of those payment sources.

On the payment side, I need to reconcile how many payments have been received and if there is an outstanding balance

Claim     Name             Payment

12345	John Smith	100
12345	John Smith	100
12345	John Smith	40
45678	Bob Jones	100
45678	Bob Jones	100

Using this sample data, John Smith/12345 has an outstanding balance of $10, while Bob Jones/45678 has an outstanding balance of $100.

I would like my final table to look something like this:

Thank you in advance for any advice

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[marcelsmaglhaes · Accepted solution]

Hey @aflintdepm ,

You will need some DAX measures and a correct data model. See the attached pbix example.

[aflintdepm]

Thank you for the example file- this was very helpful.  I just have a couple of questions

First- am I correct in my understanding that you created the third table to remove the duplicates and give a single lookup table? 

Second- could you please explain the structure of the Charge measure you created?  I don't understand the nested CALCULATE(VALUES without a filter expression)

Charge =

SUMX(

    VALUES(Charge[Claim]),

    CALCULATE(

        VALUES(Charge[Amount])

    )

)

I'm going to work to recreate this on the larger datatables that I am working with.  Thank you

[marcelsmaglhaes]

Hey @aflintdepm !

1. Yes. I put the data into a star schema model perspective

2. The CALCULATE function changes the context of the calculation. Inside the CALCULATE, we use VALUES(Charge[Claim]) to get a table with the unique values of the "Claim" column. The absence of a filter expression in CALCULATE means it operates in the current filter context established by the outer SUMX.