Forum Discussion
Computing Percentage Test Results (String) for a Certain Application (Category)
Hello,
I am looking to create a Table where I have mulitple different web apps that I support that contains a column that shows the results of a security scan as a string value of Pass or Fail. These tests run multiple times a month many times for the same app in one month, so my data may look like this:
| Application Name | Date Performed | Test Results |
| Database App | 1/16/2021 | Pass |
| Apple App | 1/17/2021 | Fail |
| Database App | 1/18/2021 | Pass |
| Apple App | 2/20/2021 | Pass |
| CRM App | 2/25/2021 | Fail |
In PowerBi, I am trying to find the pass rate (%) of each of the apps.
So I want to calculate for each distinct app: COUNT(Pass) / (COUNT("Pass")+Count("Fail")) to give me a percentage.
What I did was create 2 calulated columns:
1. Pass Column -
IF(Table1[Test Result]="Pass",1,0)
-- If Test Result = pass, then put 1 in column, else 0
2. Pass Rate Calculation -
SUM(Table1[Pass Column])/(COUNT(Table1[Pass Column]))
Explained: Sum of total passes divided by the count of tests (total tests)
The part that I am stuck on is that I need this calculation to calculate the percentage based on the Application Name.
Is there a way to specify the Application Name (Category) for the calculation?
I need my results to look like this:
| Application Name | Pass Rate (%) |
| Database App | 85% |
| CRM App | 91% |
| Apple App | 75% |
I apologize if this is confusing, I'm just starting to learn PowerBi.
8 Replies
Anonymous you are complicating the calculations, how you are getting to that % you are looking for.
In the first column, I am turning the String Value Pass into a number (1) and if the string value does not equal Pass, I am putting 0 in the row value.
Then I am taking a SUM of the column, so it is adding up all the 1's.
Then I am dividing that sum by the count of total rows in the column:
Pass/Fail 1 if Pass, 0 if fail Pass 1 Fail 0 Pass 1 So in this example, I would have 2 passes and 3 total rows. So the calculation would be 2/3 (66%)
If you create a table or matrix visual and you put Application Name and your Pass Rate measure in it, what are you currently getting?
Anonymous not sure based on the sample data how you are getting 66%, the way you are calculating it should 3/5 = 60% ( sum of 3 pass rows and total rows = 5 ) how you are showing 2/3?
Also based on your data, how you expected your final test results to be the % you are showing? You have to be very clear about what you are looking for specifically when you are showing sample data the make sure your expected output is based on the sample data.
Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Anonymous I'm referring to sample data you posted in your original post, it is 3/5, no? But you are not replying to what % you are expecting based on your original sample data?
Application Name Date Performed Test Results Database App 1/16/2021 Pass Apple App 1/17/2021 Fail
Database App 1/18/2021 Pass
Apple App 2/20/2021 Pass
CRM App 2/25/2021 Fail
Hi, Anonymous
Is your problem solevd? If not, pls let me know.
From your original description, I found that your needs are very simple, but your description and practices are confusing. How are these results calculated? We only need to know your complete calculation logic(pure text description).
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,Community Support Team _ Janey
