SUM $amount by Person ID

[Anonymous]

I have an issue to sum $amount per patient. Here is my current table:

PatientID          $Amount     DateRecieved    

  A                       100             1/1/2017       

  A                        50              10/15/2017   

  A                        10               1/1/2018 

  B                        20               1/1/2017

  B                        10               1/1/2018

  C                        10               1/1/2018

I want to create a measure "Total" that adds all $Amount per PatientID.

PatientID          $Amount     DateRecieved    Total

  A                       100             1/1/2017          160

  A                        50              10/15/2017      160

  A                        10               1/1/2018         160

  B                        20               1/1/2017          30

  B                        10               1/1/2018          30

  C                        10               1/1/2018          10

And then I want to

1- add a slicer for DateRecieved that will dynamically make Total (measure) change as the date range changes. 

2- Add Total as a filter to select e.g. Total>=30

Which will get me the following end result:

PatientID          $Amount     DateRecieved    Total

  A                       100             1/1/2017          160

  A                        50              10/15/2017      160

  A                        10               1/1/2018         160

  B                        20               1/1/2017          30

  B                        10               1/1/2018          30

I tried the following formula CALCULATE(SUM(Table1[Col 2]),ALLEXCEPT(Table1,Table1[Col 1]))  but it does not work . 

Any Help Please 🙂 ?

Thank you

[AlB]

Hi @Anonymous

Try this:

1. Place a matrix visual in your report.

2. Place Table[PatientID] in rows of the matrix

3. Place this measure in values of the matrix:

      MeasureTotal =  SUM(Table1[$Amount])

4. In 'Visual level filters', look for [MeasureTotal] and select 'Show items when the value:' --> is greater than 30

5. Place Table1[DateReceived] in a slicer

You could also (better) create a Date table and create a relationship with Table1 through DateReceived. You would then have all dates and use Date[Date] in the slicer

[Anonymous]

Thank you AIB for your reply. Your solution works perfectly if I do not add the date as you mentioned in your solution by adding it as a slicer only. But what if I want to add the date to the output value list and also add more details like ClaimNumber, ServiceLocation then it will not give the right calculations based on the date and person id . I want to add more columns to the visual but I want the sume to be summed ONLY by patient id and date regardless of the other columns listed

i.e.

PatientID          $Amount    ClaimNo         DateRecieved    Total

  A                       100              1                       1/1/2017         160

  A                        50               2                       10/15/2017     160

  A                        10               3                       1/1/2018         160

  B                        20               4                       1/1/2017          30

  B                        10               5                       1/1/2018          30

and ONLY Total column values change as I change the date slicer

[AlB · Accepted solution]

@Anonymous

I'm not quite sure I understood what you want but maybe try this for the measure then:

MeasureTotal_2 =
CALCULATE (
    SUM ( Table1[$Amount] ),
    ALLEXCEPT ( Table1, Table1[DateReceived], Table1[PatientID] )
)