Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

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

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.