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
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.
Solved! Go to 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.
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 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.
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!.
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.
User | Count |
---|---|
158 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |