cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AbhiV Frequent Visitor
Frequent Visitor

Count of specific values from a column

I want to show the count of specific value of a column from a table  as my KPI - I have used below function - 

 

CountValues =
CALCULATE ( COUNTROWS ( Sheet1 ), Sheet1[XXX_place] = " open_XXX " ) but the count is showing as 1 instead of actual count. 

 

Please let me know hat is incorrect in it. 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Count of specific values from a column

Is this a measure or a calculated column - you don’t say in the op?  The formula you posted written as a measure will correctly count the instances of “ open_XXX “ (including the spaces either side of the text).  If some of the text doesn’t have spaces either side then those rows will not be counted. You can trim the spaces using Power Query on load.  If that isn’t the problem, then I suggest you set up a test. Create a countrows(table) measure and put it in a visual with the column XXX_place on rows. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
4 REPLIES 4
AbhiV Frequent Visitor
Frequent Visitor

How can we derive count of specific value from a column of a table

I want to show the count of specific value of a column from a table  as my KPI - I have used below function - 

 

CountValues =
CALCULATE ( COUNTROWS ( Sheet1 ), Sheet1[Defecation_place] = " open_defecation " ) but the count is showing as 1 instead of actual count. 

 

Please let me know hat is incorrect in it. 

Super User
Super User

Re: Count of specific values from a column

Is this a measure or a calculated column - you don’t say in the op?  The formula you posted written as a measure will correctly count the instances of “ open_XXX “ (including the spaces either side of the text).  If some of the text doesn’t have spaces either side then those rows will not be counted. You can trim the spaces using Power Query on load.  If that isn’t the problem, then I suggest you set up a test. Create a countrows(table) measure and put it in a visual with the column XXX_place on rows. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Super User
Super User

Re: How can we derive count of specific value from a column of a table

http://community.powerbi.com/t5/Desktop/Count-of-specific-values-from-a-column/m-p/328618#M146730



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
AbhiV Frequent Visitor
Frequent Visitor

Re: Count of specific values from a column

Thanks for your reply !

 

Yes Its a measure. I am trying to show the infromation as a KPI. The visual that i choosed is Card , after creating this a new column measure with the logic that I have mentioned in the earlier post. Let me try the test you suggested and check the result.