cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AgeOfEgos Frequent Visitor
Frequent Visitor

SUM by Person ID

I have a table that will have multiple rows per Person ID that I would like to sum and then filter.  For example;

 

Patient A                90

Patient A              100

Patient A                10

Patient B                50

Patient B                50

 

I'd like to create a total for each patient that would take the above and make a new column called "Total" like;

 

Patient A           200

Patient B           100

 

I'd then apply a visual level filter to show me only the patients with a total above 100.  Thanks for any help.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: SUM by Person ID

you could drop the patients and the Office Visit in a table visual, then drop the measure i posted earlier and the drop this other measure = CALCULATE( SUM( Table[Amount] ), ALL( Table[Office Visit] ) )

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

4 REPLIES 4
Super User
Super User

Re: SUM by Person ID

You can easily do it by dropping Patients on the rows section of a Matrix visual and then adding the measure

=SUM( Patients[Amount] )

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

AgeOfEgos Frequent Visitor
Frequent Visitor

Re: SUM by Person ID

Hey Livio,

 

Thanks!  That works--except if I want to see the individual rows that contributed to that total, once I add them back it sums them individually.  For example if I had;

 

Patient A                200                Office Visit

Patient A                100                Labs

Patient B                200                Radiology

Patient B                100                Labs

 

If I use the sum measure, I'd get;

 

Patient A             300           

Patient B             300

 

How would I go about this if I wanted to see;

 

Patient A                200                Office Visit             300

Patient A                100                Labs                       300

Patient B                200                Radiology              300

Patient B                100                Labs                       300

 

Or is it best to use a drill down feature?  Thanks!

Super User
Super User

Re: SUM by Person ID

you could drop the patients and the Office Visit in a table visual, then drop the measure i posted earlier and the drop this other measure = CALCULATE( SUM( Table[Amount] ), ALL( Table[Office Visit] ) )

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Re: SUM by Person ID

try to use

new col = SUMX(FILTER('table';EARLIER('table'[col1])='table'[col1]);'table'[col2])