cancel
Showing results for
Did you mean:
Helper II

## Calculate Count based on sum not equal to 0

Hi PB buddy,

I would like to have a measure as such result, would anyone help, thanks

count column A where sum of column B is not 0, the outcome should be 2

Many thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

## Re: Calculate Count based on sum not equal to 0

Hi,

These measures work

Measure = COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[Customer]),Data[Customer],"ABCD",CALCULATE([Total],Data[Code]=1)),[ABCD]<>0))
Measure 2 = COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[Customer]),Data[Customer],"ABCD",CALCULATE([Total],Data[Code]=1),"EFGH",CALCULATE([Total],Data[Code]=2)),[ABCD]<>0&&[EFGH]<>0))
Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
7 REPLIES 7
Resolver II

## Re: Calculate Count based on sum not equal to 0

Hi @SimonChung_GGGG  ,

Cheers,
Rob

Helper II

## Re: Calculate Count based on sum not equal to 0

Hi @RobinDeFal , thanks for your reply, pardon English is not my primary language, I've already searched previous post but can't get a right answer, I've made my question as simple as possible as well.

Highlighted
Resolver II

## Re: Calculate Count based on sum not equal to 0

Hi @SimonChung_GGGG  ,

No problem.

1. Please share sample data via copy/paste (no picture) or share .pbix file.

Cheers,

Rob

Helper II

## Re: Calculate Count based on sum not equal to 0

Thanks @RobinDeFal

1. Please find the sample data below

 Customer Amount Code A0001 100 01 A0001 -100 01 A0002 100 01 A0002 100 01 A0002 0 02 A0002 0 02 A0003 100 01 A0003 100 01 A0003 200 02 A0003 200 02 A0004 200 02 A0004 200 02

2. now I would like 2 measures which
measure 1 = count no. of customer where code = "01" and sum of amount <> 0
desired result = 2 (A0002, A0003)
measure 2 = count no. of customer where (code = "01" and sum of amount <> 0) and (code = "02" and sum of amount <> 0)
desired result = 1 (A0003)

Simon

Super User IV

## Re: Calculate Count based on sum not equal to 0

Hi,

These measures work

Measure = COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[Customer]),Data[Customer],"ABCD",CALCULATE([Total],Data[Code]=1)),[ABCD]<>0))
Measure 2 = COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[Customer]),Data[Customer],"ABCD",CALCULATE([Total],Data[Code]=1),"EFGH",CALCULATE([Total],Data[Code]=2)),[ABCD]<>0&&[EFGH]<>0))
Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

## Re: Calculate Count based on sum not equal to 0

Great thanks bro! It's help a lot!

Super User IV

## Re: Calculate Count based on sum not equal to 0

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors