Having trouble assessing: Custom Measure / New Table/ Append?

[AW1976NOVA]

Hi,

I have an excel table 'Claim Data' I'm bringing in to Power BI.  The table has medical claim data.  The two fields that apply to my question here are [Plan Payment Amount] and [Claim Serial Number].  Each [Claim Serial Number] can populate numerous times in this table (meaning a customer could have gone to the same doctor for the same cold multiple times and it would have been logged with the same [Claim Serial Number] because it was for the same issue.  Each instance (row) of the [Claim Serial Number] is also populated with a [Plan Payment Amunt].  For example:

Claim Serial Number     Plan Payment Amount]

0077                              $1.00

0077                              $3.00

0077                              $10.00

0050                              $150.00

0077                              $10.00

0030                              $25.00

With that in mind, Id like to associate certain [Plan Payment Amount] totals for each [Claim Serial Number] as either a (High Cost Claimant), a (Mediam Cost Claimant) or neither.

For this example, lets pretend that a (High Cost Claimant) is any situation where a [Claim Serial Number]'s total [Plan Payment Amount] is greater than or equal to $150.00.  Let's also pretend that a (Medium Cost Claimant) is where a [Claim Serial Number]'s total [Plan Payment Amount] is greater than or equal to $25.00 but less than $150.00.  In any other situation (if the [Claim Serial Number]'s total [Plan Payment Amount] is less than $25.00...it is neither a (High Cost Claimant) or {Medium Cost Claimant).

For this example, [Claim Serial Number] 0050 is the only (High Cost Claimant).  [Claim Serial Number] 0030 is the only (Medium Cost Claimant) while 0077 is neither a (High Cost Claimant) or (Medium Cost Claimant).

I'm not really sure how I should go about building this out.  Here is a picture of my relationships already.

Thank you in advance,

Andrew

[FrankAT · Accepted solution]

Hi @AW1976NOVA,

take a look at the following solution:

Regards FrankAT