cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SimonChung_GGGG Helper II
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

 

image.png

 

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
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.
Untitled.png

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
RobinDeFal Resolver II
Resolver II

Re: Calculate Count based on sum not equal to 0

Hi @SimonChung_GGGG  ,

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Cheers, 
Rob

SimonChung_GGGG Helper II
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
RobinDeFal Resolver II
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.

2. Please share what is your expected result from sample data.

 

Cheers,

Rob

SimonChung_GGGG Helper II
Helper II

Re: Calculate Count based on sum not equal to 0

Thanks @RobinDeFal 

1. Please find the sample data below

CustomerAmountCode
A000110001
A0001-10001
A000210001
A000210001
A0002002
A0002002
A000310001
A000310001
A000320002
A000320002
A000420002
A000420002

 

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)

 

Many thanks in advance

Simon

Super User IV
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.
Untitled.png

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

SimonChung_GGGG Helper II
Helper II

Re: Calculate Count based on sum not equal to 0

Great thanks bro! It's help a lot!

Super User IV
Super User IV

Re: Calculate Count based on sum not equal to 0

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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