cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jonnokc
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
Greg_Deckler
Super User IV
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.


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Greg_Deckler
Super User IV
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.


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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?

ALeef
Advocate III
Advocate III

Measure:

 

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

 

I think syntax is right, but not positive.

Helpful resources

Announcements
MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors