Counting Employees between Values

[novotnajk]

Hello!

I am trying to aggregate and count patients based on their claims spend.

Here is a sample table:

patient_id         Medical     Rx

ABC123             $50,000    $10,000 

DEF123             $25,000    $5,000              

HIJ123              $10,000    $1,000              

I then created a Total Spend measure:  Total Spend:  calculate(sum(Patient[medical)+calculate(sum(Patient[Rx).  This yields a result for Patient ABC123 of $60,000, DEF123 $30,000, and HIJ123 $11,000.  

I'm good up to this point.

Lastly, I created a table that is being used as a filter to give me claim spend ranges:

claim_range

$10,000

$20,000

$30,000

$40,000

$50,000

$60,000

$70,000

$80,000

$90,000

$100,000

Lastly, I created a measure based on the selected claim_ranges above:  Claim Range:  calculate(average(Claim[claim_range])

This is used as a filter to adjust the formulas below.

#1:  First formula seems to work just fine

(H1) # High Cost Patients= CALCULATE (

COUNTROWS (

FILTER (

SUMMARIZE ( 'Patients', 'Patients'[patient_id], "Aggregate", [Total Spend]),

[Aggregate] >= [Claim Range]

When I filter $50,000 from the claim range filter I created, the value shows 1 patient.  This works great.

But I need to create another measures for patients who are at 50% of the filtered Claim Range and exclude those that are above the Claim Range already.

My first attempt was to take the Claims Range measure and apply the following"  Claim Range 50%: [Claim Range]*.50

(H1) # High Cost Patients 50%= CALCULATE (

COUNTROWS (

FILTER (

SUMMARIZE ( 'Patients', 'Patients'[patient_id], "Aggregate", [Total Spend]),

[Aggregate] >= [Claim Range 50%])))-(H1) # High Cost Patients

This doesn't seem to calculate correctly.  Is there a way to create a formula that looks like this?:

(H1) # High Cost Patients 50%= CALCULATE (

COUNTROWS (

FILTER (

SUMMARIZE ( 'Patients', 'Patients'[patient_id], "Aggregate", [Total Spend]),

[Aggregate] < [Claim Range] >= [Claim Range 50%])))

Any guidance would be appreciated!

[Anonymous · Accepted solution]

Hi @novotnajk ,

Check the formulas.

Claim Range = calculate(average(Claim[claim_range]))

Claim Range 50% = [Claim Range]*.50

High Cost Patients = CALCULATE(DISTINCTCOUNT(Patient[patient_id]),FILTER(Patient,Patient[Total Spend]>=Claim[Claim Range]))

High Cost Patients 50% = CALCULATE(DISTINCTCOUNT(Patient[patient_id]),FILTER(Patient,Patient[Total Spend]>=Claim[Claim Range 50%]&&Patient[Total Spend]<Claim[Claim Range]))

Best Regards,

Jay

[novotnajk]

Hi Ashish,

I was hoping to avoid another filter (proportion).  If there a way to build the proportion you've highlighted above into a measure?

[Ashish_Mathur]

Hi,

The input of 50% or 40% has to be a user input - that's why i built that table manually.

[novotnajk]

Sorry, meant patients, not employees.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.  The table on the right will show you the customers who spent between 30,000 and 60,000.  From the third table, if you wish, you may remove the Patient_ID field.

Hope this helps.