cancel
Showing results for
Did you mean:
Frequent Visitor

## Percent of text values in a column

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.

1 ACCEPTED SOLUTION
Super User IV

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.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

4 REPLIES 4
Super User IV

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.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Frequent Visitor

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.

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?

Measure:

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

I think syntax is right, but not positive.

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.