Sum of distinct rows

[Anonymous]

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 · Accepted solution]

@v-shex-msft @vanessafvg

Thanks for your help. I solved it now like this:

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

Best,

Micha

[v-shex-msft]

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 · Accepted solution]

@v-shex-msft @vanessafvg

Thanks for your help. I solved it now like this:

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

Best,

Micha

[vanessafvg · Accepted solution]

you actually dont need the filter

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

[vanessafvg]

@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