cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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
Solution Sage

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] ) )

Proud to be a Datanaut!

4 REPLIES 4
Solution Sage

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

=SUM( Patients[Amount] )

Proud to be a Datanaut!

Helper I

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                100                Labs

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

Patient A             300

Patient B             300

Patient A                200                Office Visit             300

Patient A                100                Labs                       300

Patient B                100                Labs                       300

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

Resolver V

try to use

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

Solution Sage

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] ) )

Proud to be a Datanaut!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors