cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

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
Member

Re: Calculate Count based on sum not equal to 0

Hi @SimonChung_GGGG  ,

Cheers,
Rob

Regular Visitor

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.

Member

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

Regular Visitor

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
Regular Visitor

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

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!