cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Count by Criteria

Hi All,

 

 

need help in count in criteria:-

 

<30

>=30 &<70

>=70

 

SubNo.      
A20  Count by criteria   
A29  Sub<30>=30 & <70>=70
A31  A253
A35  B433
A55      
A80      
A85      
A75      
A45      
A40      
B10      
B15      
B17      
B28      
B40      
B45      
B56      
B80      
B91      
B78      

 

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
Super User

Re: Count by Criteria

@Naveennegi119 

 

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

 

 

 

Capture.PNG

 


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

Re: Count by Criteria

@Naveennegi119 

 

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

 

 

 

Capture.PNG

 


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

Re: Count by Criteria

Thanks for sharing Idea @vanessafvg

 

Regards,

NICK

Re: Count by Criteria

Hi @vanessafvg

 

Can it possible if criteria range is not filfull condition.

 

it show 0 instead of blank

 

SubCount<30>=30 - <70>=70
A105 5
B10532

 

See in A >=30 - <70 catagory

 

Regards,

NICK

Highlighted
Super User
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!

Re: Count by Criteria

Hi @vanessafvg,

 

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

 

but thanks for help

 

 

Regards,

NICK

Super User
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!

Re: Count by Criteria

@vanessafvg

 

I'm using Chiclet slicer,

 

problem is

 

Selected Apple in slicer      
 ChicletSlicer  Sub<30>=30 - <70<=70 
 Apple  A5 5 
 Mango  B532 
 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  A505 
 Mango  B532 
 Banana  A000 
    B000 
    A000 
    B000 

 

showing apple value with 0, that's good

but also show other data value with 0

 

Regards,

NICK