cancel
Showing results for
Search instead for
Did you mean:
Naveennegi119 Member

## Count by Criteria

Hi All,

need help in count in criteria:-

<30

>=30 &<70

>=70

 Sub No. A 20 Count by criteria A 29 Sub <30 >=30 & <70 >=70 A 31 A 2 5 3 A 35 B 4 3 3 A 55 A 80 A 85 A 75 A 45 A 40 B 10 B 15 B 17 B 28 B 40 B 45 B 56 B 80 B 91 B 78

It's easy in excel but in power BI i can't figure out this.

Best regards,

NICK

1 ACCEPTED SOLUTION

Accepted Solutions Super User

## Re: Count by Criteria

Measure > 30 = CALCULATE(COUNTROWS(Table2),Table2[No.] < 30)

Measure 30&70 = CALCULATE(COUNTROWS(Table2),Table2[No.] >= 30, Table2[No.] < 70)

Measure70 = CALCULATE(COUNTROWS(Table2),Table2[No.] >=70)

that should give you the idea

and gives this result Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
7 REPLIES 7 Super User

## Re: Count by Criteria

Measure > 30 = CALCULATE(COUNTROWS(Table2),Table2[No.] < 30)

Measure 30&70 = CALCULATE(COUNTROWS(Table2),Table2[No.] >= 30, Table2[No.] < 70)

Measure70 = CALCULATE(COUNTROWS(Table2),Table2[No.] >=70)

that should give you the idea

and gives this result Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Naveennegi119 Member

## Re: Count by Criteria

Thanks for sharing Idea @vanessafvg

Regards,

NICK

Naveennegi119 Member

## Re: Count by Criteria

Can it possible if criteria range is not filfull condition.

it show 0 instead of blank

 Sub Count <30 >=30 - <70 >=70 A 10 5 5 B 10 5 3 2

See in A >=30 - <70 catagory

Regards,

NICK Super User

## Re: Count by Criteria

@Naveennegi119 Measure70 = CALCULATE(COUNTROWS(Table2)+0,Table2[No.] >=70)

you can just add +0

you can also use an if statement with isblank but adding + is easier

Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Naveennegi119 Member

## Re: Count by Criteria

Hi @vanessafvg,

it's working but not usable with filter or slicer.

but thanks for help

Regards,

NICK Super User

## Re: Count by Criteria

@Naveennegi119what are you filtering on?

Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Naveennegi119 Member

## Re: Count by Criteria

@vanessafvg

I'm using Chiclet slicer,

problem is

 Selected Apple in slicer ChicletSlicer Sub <30 >=30 - <70 <=70 Apple A 5 5 Mango B 5 3 2 Banana Measure >30 = CALCULATE(COUNTROWS('FT Test Result'),'FT Test Result'[Percentage]<30) After using this formula Measure >30 = CALCULATE(COUNTROWS('FT Test Result')+0,'FT Test Result'[Percentage]<30) Selected Apple in slicer ChicletSlicer Sub <30 >=30 - <70 <=70 Apple A 5 0 5 Mango B 5 3 2 Banana A 0 0 0 B 0 0 0 A 0 0 0 B 0 0 0

showing apple value with 0, that's good

but also show other data value with 0

Regards,

NICK