Percent of text values in a column

[Jonnokc]

Hello,

Would anyone be able to provide some advice about how to find the percentage of specific text values in a column?

For example if I have a column that contains text values of either, "yes" or "no" and I want to find out the percentage of "no" and "yes" values against the total values for the column.

[Greg_Deckler · Accepted solution]

Lots of potential ways of doing this, one way would be to create a new column like this:

CountofYes=IF(IFERROR(SEARCH("yes",[YesNo]),0),1,0)

This will give you 1 in the column for all the yes values and 0 for anything else.

You could then create a measure that did a SUM on the "CountofYes" column divided by the COUNT of your records to get you a percentage. Percent of "no" would be 1 minus the new measure.

Something like that.

[Jonnokc]

I was hoping to find a solution in which I could keep the % of each value in the same table so I could create a table with one column that shows the % for each value. Is this possible? 

I usually perform this task  in Access. I can obviously still do this. I am just seeing if it is possible to use Power Bi for all my needs instead of two programs.

[ALeef]

Using a measure instead of a column should give you the result you are looking for.  That measure will be always up to date based on that table.  So if you have 50% Yes, and then add some more rows with Yes, the Percentage will increase accordingly.

Then you can call against that measure in other visualizations - so if you had a Sales Table and a Customer Table, you could create the measure both places, and then see your Sales[PctYes] vs Customer[PctYes].

Can you give us an example of how you will be using it?

[ALeef]

Measure:

PctYes = (Calculate(counta('Table'[Response]), 'Table'[Response] = "Yes")) / (Countrows ('TableName')

I think syntax is right, but not positive.

[Gendy]

I've been searching for this for the past 4 days, and you the only one who wrote for me the right equation. THANK YOU!.