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 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   
citygroup_idgroup_membersdate_visit
NYC123102022-01-01
NYC123102022-01-02
LA123102022-01-03
LA234202022-01-03

The result in pivot table should be:

citygroup_members (the sum of the members of unique group_id
NYC10
LA30

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

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

3 Replies

  • onurbmiguel_'s avatar
    onurbmiguel_
    Power 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_
        Power Participant
        4 years ago

        hi again, 

        if possible accpet my post as solution.

        Appreciate your Kudos!! ;-

         

        Best Regards
        BC