Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Measure: identify unique rows before sum is calculated

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 outp...
Need Help
  • onurbmiguel_'s avatar
    onurbmiguel_
    4 years ago

    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

onurbmiguel_'s avatar
onurbmiguel_
Icon for Power Participant rankPower Participant
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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:

citygroup_members (the sum of the members of unique group_id
NYC10
LA40
SUM60
  • onurbmiguel_'s avatar
    onurbmiguel_
    Icon for Power Participant rankPower Participant
    4 years ago

    hi again, 

    if possible accpet my post as solution.

    Appreciate your Kudos!! ;-

     

    Best Regards
    BC