Forum Discussion

novotnajk's avatar
novotnajk
Resolver I
4 years ago
Solved

Counting Employees between Values

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!

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

5 Replies

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      4 years ago

      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.

  • novotnajk's avatar
    novotnajk
    Resolver I
    4 years ago

    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's avatar
      Ashish_Mathur
      Super User
      4 years ago

      Hi,

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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