Count Distinct where sum is

[BI-Bola]

I am trying to get a distinct count of values where the sum of values is not zero

Below I have a basic distinct count: 

Distinct Count = CALCULATE(DISTINCTCOUNT(AccountTransactions[Segment]), AccountTransactions[Amount] <> 0)

How would I create a similar function where the I return a distinct count for "Segment" where the SUM of "Amount" is not 0?

For the sake of clarity a sample table is below.

Segment	Amount	var1	var2
A	10	example	random1
A	1	example 2	random 2
B	0	example 3	random 3
C	1	example 4	random 4
D	2	example 5	random 5
D	2	example 6	random 6

For the above table a distinct count of segment values where the SUM of the amount column is not zero would be 3.

I want to display this information in a card. I have tried the below formula but it will not work.

CALCULATE(COUNTROWS(distinct(AccountTransactions[Segment4])), FILTER(AccountTransactions, SUM(AccountTransactions[Amount])<>0))

After some thought I realised that this is because while the rows have been narrowed with "distinct" it isn't eliminating duplicate "Segment" lines as the additional columns contain unique information foreach row. So my result is a much larger value. 

I could create a mirrored table that only pulls through the "Segment" and run the formula based on the relationship between that and the main table but I want to know if there's another way. 

Thanks

[BI-Bola · Accepted solution]

Thanks for your suggestions all but strangely none worked with my dataset. I'm not sure if it's the size of the dataset or the fact that it is running through a direct query causing the issue.

To get around it I created an additional table from the main query using a combination of the CALCULATETABLE, SUMMARIZE and FILTER functions and put a count card visual pulling data from new table.

[BI-Bola]

I have expanded the example table so that it is more in line with my dataset, apologies if it was unclear.

@V-lianl-msft 

Thanks for the response but your formula does't seem to work for the updated table

[V-lianl-msft]

Hi @BI-Bola ,

After my test, I can get the expected results.

If my understanding is wrong, please share the result you expect.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tan_Bui]

Thanks a lot. I have tried this for my case with conditional distinct count and it worked 🙂

[Anonymous]

If this resolves your solution mark it as a solution so that it will help others and don't hesitate to give kudos.

Thanks,

Pravin

[Anonymous]

Hi @BI-Bola 

Heve you tried the solution which i suggested.

measure= Sumx(summerize(table,table[sagement],"Total",sum(table[Amount])),If(Total>0,1,0))

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[V-lianl-msft]

Hi @BI-Bola ,

You can try the DAX below:

Measure = CALCULATE(COUNTROWS(DISTINCT('sample'[Segment])),FILTER('sample','sample'[Amount]<>0))

Here is the sample pbix.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Create measure

measure= Sumx(summerize(table,table[sagement],"Total",sum(table[Amount]),If(Total>0,1,0))

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[BI-Bola]

Doesn't seem to work, spelling of functions were corrected

MEASURE = SUMX(SUMMARIZE(AccountTransactions,AccountTransactions[Segment],"Total", SUM(AccountTransactions[Amount]),If(Total>0),1,0))

error "The syntax for ')' is incorrect."

[Anonymous]

it's my bad.

I forgot to close bracket.

measure= Sumx(summerize(table,table[sagement],"Total",sum(table[Amount])),If(Total>0,1,0))

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[BI-Bola]

@Anonymous 

Original post updated

@vanessafvg 

I am after a distinct count of the segment column values where the sum of "Amount" is not equal to zero

[vanessafvg]

do you want a distinct count or row count?  distinct count on segment will just count the segment?

[Anonymous]

share some sample data and expected output.