Measure: identify unique rows before sum is calculated

[Anonymous]

Hi everyone,

hope you all are doing well. I am looking for a measure for a pivot table. Data records with identical entries in certain columns (here: city and group_id) are to be identified and output as one entry. So if I want to make a sum, values that occur more than once may only be considered once.

table: visit
city	group_id	group_members	date_visit
NYC	123	10	2022-01-01
NYC	123	10	2022-01-02
LA	123	10	2022-01-03
LA	234	20	2022-01-03

The result in pivot table should be:

city	group_members (the sum of the members of unique group_id
NYC	10
LA	30

My almost successful attempt was =SUMX(DISTINCT(Visit[group_id]);MAX(Visit[group_members])). If I use this measure for the pivot-table, the amount of group_members is too high. What confuses me is, when I use column group_id instead of city, the correct group_members have been calculated.

I would be very happy about some help.

Best, Raphael

[onurbmiguel_ · Accepted solution]

Hello MrRabbitTrick

try this measure: 

Measure = 
var _temp = 
    SUMMARIZE(
        'Table',
        'Table'[City],
        'Table'[group_id],
        "@total",MAX('Table'[group_members])
    )

return 
SUMX(_temp,[@total])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BC

[Anonymous]

Hi @onurbmiguel_ ,

many thanks for your help. I changed the measure for using in Power Pivot as follows

= SUMX(
    SUMMARIZE(
        'Table',
        'Table'[City],
        'Table'[group_id]),
        MAX('Table'[group_members])
    )

I think, the measure works the same. The result ist half right. For LA there are 10 members too many, and the sum says 60 members:

city	group_members (the sum of the members of unique group_id
NYC	10
LA	40
SUM	60

[onurbmiguel_]

hi again, 

if possible accpet my post as solution.

Appreciate your Kudos!! ;-

Best Regards
BC