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
vanessafvg Super Contributor
Super Contributor

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!

View solution in original post

7 REPLIES 7
vanessafvg Super Contributor
Super Contributor

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!

View solution in original post

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

vanessafvg Super Contributor
Super Contributor

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

Re: Count by Criteria

Hi @vanessafvg,

 

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

 

but thanks for help

 

 

Regards,

NICK

vanessafvg Super Contributor
Super Contributor

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 360 members 3,245 guests
Please welcome our newest community members: