Display number of people with same total.

[j12499901313r9]

Person	Unit
Bob	1
Bob	1
Tom	2
Bill	1

My data has a Person column with duplicated names and a Unit column with values. I would like to sum the Unit column by Person and then display the number of people that have any given total. So for the example above 2 people have a total of 2 and 1 person has a total of one. I feel like I'm making this more complicated than necessary. Thanks!

[v-weiyan1-msft · Accepted solution]

Hi @j12499901313r9 ,

Based on the sample and description you provided, Please try code as below to create two measures.

Unit Total by Person = CALCULATE(SUM('Table'[Unit]), ALLEXCEPT('Table','Table'[Person]))

People Count by Unit Total = CALCULATE(COUNTROWS(DISTINCT('Table'[Person])),FILTER('Table','Table'[Unit Total by Person]))

Result is as below.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jayaselvan]

Hi,

Hope this helps.

People Count by Unit Total =
VAR _Totals = SUMMARIZE(Table, Table[Person], "Total Units", SUM(Table[Unit]))
RETURN
COUNTROWS(
FILTER(_Totals, [Total Units] = SELECTEDVALUE(_Totals[Total Units]))
)

[j12499901313r9]

Thank you for the assistance. When I try to use this with the test data, I get an error of "Parameter is not the correct type. Cannot find name '[Total Units]'