Forum Discussion

Anonymous

Not applicable

9 years ago

Solved

Sum of distinct rows

Hi,

there are several similar cases, but all these solutions didnt work out for me. Maybe just a simple fault by me.

My data is given like this:

ID / Customer / gender (1=man, 0=woman) /

123 ABC 1

123 ABC 1

124 ABD 0

125 ABE 1

123 ABC 1

126 ABF 0

I want to summarize the values given in gender but only for distinct rows.

In this case: 123 should not summarized as 3 - correct would be 1

My suggestion: SUMX(DISTINCT(ID);SUM(GENDER))

Thanks for your help!

Anonymous
9 years ago
Anonymous vanessafvg
Thanks for your help. I solved it now like this:

CALCULATE(DISTINCTCOUNT(ID);FILTER(TABLE;Gender=1))

Best,
Micha
vanessafvg
9 years ago
you actually dont need the filter

CALCULATE(DISTINCTCOUNT(ID),TABLE[Gender]=1) should work fine

4 Replies

vanessafvg
Community Champion
9 years ago
Anonymous
cases = distinctcount(id)

if you put your gender on your visual then it should sum up the distinct cases

i.e based on your data

Gender No of Cases
1 2
0 2
Anonymous
Not applicable
9 years ago
Hi Anonymous,

You can use countx and distinct function to achieve your requirement.

Measure:

Distinct=COUNTX(DISTINCT(ALL(Table1)),[Gender]))

Sample:

Table 2 = SUMMARIZE('Table1',[Gender],"Count",COUNTX(DISTINCT(Table1),[Gender]))

Regards,

Xiaoxin Sheng
- Anonymous
  Not applicable
  9 years ago
  Anonymous vanessafvg
  Thanks for your help. I solved it now like this:
  
  CALCULATE(DISTINCTCOUNT(ID);FILTER(TABLE;Gender=1))
  
  Best,
  Micha
  - vanessafvg
    Community Champion
    9 years ago
    you actually dont need the filter
    
    CALCULATE(DISTINCTCOUNT(ID),TABLE[Gender]=1) should work fine

Related content