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     ...
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

novotnajk's avatar
novotnajk
Resolver I
4 years ago

Sorry, meant patients, not employees.

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.